Blog
Microsoft SQL 2012 Developer: Creating Partitioned Tables
By Frank Tillinghast | February 04, 2014
SQL Server 2012 includes the T-SQL extensions to allow for partitioned tables and indexes. Here are the specific steps you perform to create and maintain these database objects. To create a partitioned table, complete the following tasks:
- Select an appropriate partition key.
- Create a partition function.
- Create a partition scheme.
- Create the table and identify its partition scheme.
| Key Terms | |
|---|---|
| Partition key | A single column in a table whose values determine the partition on which the data resides. |
| Partition function | A function that specifies how to partition the table by defining partition key boundaries for each subset of data. |
| Partition scheme | A mapping of individual partitions to filegroups. |
Creating a Partition Function
With a partition key selected, the next step is to define the partitions. The CREATE PARTITION FUNCTION statement specifies how to divide the table by identifying specific boundaries for each partition. The basic syntax of the CREATE PARTITION FUNCTION statement is:
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES (boundary_value_1, boundary_value_2 ...) Note: the following syntax details: The input_parameter_type specifies the data type of the partition key and not the name of the partitioning column. The LEFT and RIGHT keywords specify the side of a partition boundary on which a boundary_value belongs. LEFT is the default if you do not explicitly state a side.
LEFT and RIGHT Boundaries
The best way to understand the difference between LEFT and RIGHT partition boundaries is to look at some examples. Consider the following two partition functions:
CREATE PARTITION FUNCTION ExampleRangeLeftPF(int) AS RANGE LEFT FOR VALUES (100, 1000, 10000, 100000) CREATE PARTITION FUNCTION ExampleRangeRightPF(int) AS RANGE RIGHT FOR VALUES (100, 1000, 10000, 100000) Table below shows the specific partition boundaries for both LEFT and RIGHT versions of the partition function.
| Partition | Column Values (LEFT) | Column Values (RIGHT) |
|---|---|---|
| 1 | <= 100 | < 100 |
| 2 | > 100 AND <= 1,000 | >= 100 AND < 1,000 |
| 3 | > 1,000 AND <= 10,000 | >= 1,000 AND < 10,000 |
| 4 | > 10,000 AND <= 100,000 | >= 10,000 AND < 100,000 |
| 5 | > 100,000 | >= 100,000 |
The table above Partition range values defined according to LEFT and RIGHT boundaries. A partition function that you define using LEFT boundaries indicates that the boundary value exists in the partition to the value's left. Similarly, RIGHT boundaries place values in the partition to the value's right. Because all partition key values need to map to a partition, SQL Server actually creates n+1 partitions for every partition function. Notice that both functions specify only four boundary values. However, values less than the minimum and greater than the maximum are part of the entire domain of values and therefore must map to a partition.
TIP: If you need to specify acceptable minimum or maximum values, apply a CHECK constraint on the table to reject inappropriate partition key values. For example, to prevent partition key values greater than 100,000, define the partition function by using RANGE LEFT and placing a CHECK constraint on the table to reject values greater than 100,000.In addition, a CHECK constraint on the table effectively leaves either the leftmost or the rightmost partition empty-a benefit if you add or remove partitions later.
Although not ideal, SQL Server allows partition key columns to contain NULL values and handles them using these rules: By default, all records that have a partition key value of NULL exist on the leftmost partition. If a partition function contains a NULL boundary value and is defined using RANGE RIGHT, the leftmost partition remains empty and rows that contain NULL values exist on the next partition. Keep these considerations in mind when you design partition functions.
