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.
First off you will need Enterprise SQL Server in your production environment, or alternatively you can explore this feature in SQL Developer edition.
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.
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.
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