SQL Server Partitioning Overview

Introduction

This is a guide to help explain partitioning setup; which although introduced way back in SQL 2005 is still an area which throws up a lot of questions.

Requirements

First off you will need Enterprise SQL Server in your production environment, or alternatively you can explore this feature in SQL Developer edition.

Getting Started

In order to partition there are two objects that you will first need to create these are a PARTITION FUNCTION and a PARTITION SCHEME.

Function

The partition function is the first building block that you need and requires that you specify boundaries.

CREATE PARTITION FUNCTION pfL(INT)
AS RANGE LEFT
FOR VALUES (100,200,300);

The syntax is simple and shows that in the above case the function expects an integer and will create boundaries based on a RANGE LEFT strategy. The example shows three boundaries which will mean that there are four partitions. Number of boundaries + 1.

RANGE LEFT means that the boundary points are <= less than or equal. In the example below the first boundary is 100 which means all values less than or equal to 100 will go in to the first partition, those above 100 and less than the next boundary will fit in to partition two (P2) and so on.

The below image shows this and with example values sorted.

LeftPartition

Now obviously having shown RANGE LEFT, here is a RANGE RIGHT example. Note that now the boundaries points are => greater than or equal to. So the value 100 for example is now in the second partition.

RightPartition

Scheme

Before we can apply the partition, a partition scheme needs to be deployed which will identify the file groups to use.

CREATE PARTITION SCHEME psL
AS PARTITION pfL TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

The table below specifies that it will use the partition scheme and will be partitioned on the ID column and to make this example simple all the data is stored on the Primary file group.

CREATE TABLE L ([ID] INT) ON psL([ID])
INSERT INTO L VALUES (NULL)
INSERT INTO L VALUES (-1)
INSERT INTO L VALUES (0)
INSERT INTO L VALUES (1)
INSERT INTO L VALUES (100)
INSERT INTO L VALUES (101)
INSERT INTO L VALUES (200)
INSERT INTO L VALUES (201)
INSERT INTO L VALUES (300)
INSERT INTO L VALUES (301)
INSERT INTO L VALUES (1001)

You can check where each row is using the Detailed Table and Index Breakdown script

One thought on “SQL Server Partitioning Overview

  1. Pingback: SQL Server Partitioning Data Movement | Jon Gurgul's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *