When we use LEFT clause in the range, the first partition could hold a value from negative infinity to 100, the second partition could hold values from 101 to 200, the third partition could hold values from 201 to 300 and the fourth partition could hold values from 301 to Infinity or whatever the max range of the integer value.

When we use RIGHT clause in the range, the first partition could hold a value from negative infinity to 99, the second partition could hold values from 100 to 199, the third partition could hold values from 200 to 299 and the fourth partition could hold values from 300 to Infinity or whatever the max range of the integer value.

Execute the following query to see the partition information.

SELECT * FROM sys.partition_range_values

Step 4

Partition Function is not useful unless it is associated with the proper file groups that we have created.

Let us assume that we are going to use file group [Data Partition DB FG1],

[Data Partition DB FG2], [Data Partition DB FG3], [Data Partition DB FG4] for the partition table that we are going to create. This can be created as shown below.

Now let us create the actual table that we want to partition, using the created partition scheme. The CREATE TABLE statement should contain the partition key and the partition scheme to be used. This can be created as shown below.

Now let us create an index on the partitioned table. An index on a table improves performance. When both the indices and the table use the same partitioning function and the same partitioning columns, the table and index are said to be aligned. This can be created as shown below.

Conclusion:

This article illustrated Data partitioning, the new feature introduced in SQL Server 2005 by Microsoft. This part of the series illustrated how to partition a table based on different ranges. The subsequent article in this series will illustrate how to modify the partition function and partition schemes and how to handle file groups, etc.