SQL2005: Partitioning

The most common way to improve the performance of our queries to large tables containing both current and historical data is to use horizontal partitioning. Prior to SQL Server 2005, this strategy can be materialized with a partitioned view that union multiple copies of a table with the same structure that contain sets of horizontally partitioned data.

However in SQL Server 2005, there is now a new feature called Partitioned Tables. The data contained in partitioned tables can be horizontally spreaded across one or more filegroups in the database and these filegroups can be deployed to several disks to improve performance. This feature is not limited to tables alone, indexes too can be partitioned.

To demonstrate this feature, we start by defining a partition function.

CREATE PARTITION FUNCTION MonthPartition(int)
AS RANGE FOR VALUES (3, 6, 9)

The partition function specifies that four partitions (<=3, 4-6, 7-9, >9) are to be created and it only applies to an int column.

Next, create the partition scheme.

CREATE PARTITION SCHEME MonthScheme
AS PARTITION MonthPartition
TO (q1fg, q2fg, q3fg, q4fg)

The above partition scheme specifies that all the four partitions defined in the partition function will be spreaded across four filegroups (Example assumes that the filegroups have already been created).

Finally, create the partitioned table.

CREATE TABLE Orders (OrderID int, OrderMonth int, OrderDate DateTime)
ON MonthScheme (OrderMonth)

That's all to it.