Partition Function in SQL Server

The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold.

This is the first step in creating partitions

The syntax is as follows

CREATE PARTITION FUNCTION < Function Name > (< DataType >) AS RANGE LEFT/RIGHT FOR VALUES (< value1 >,< value2 >,…)

LEFT : This specifies the maximum value of the first partition

RIGHT : This specifies the minimum value of the second partition.


The below examples shows creating a partitions as follows

Partition 1 : column1 <= 1
Partition 2 : column1 > 1 and column1 <= 100
Partition 3 : column1 > 100 and column1 <= 1000
Partition 4 : column1 > 1000

CREATE PARTITION FUNCTION EmployeePartitionLeft (int) AS RANGE LEFT FOR VALUES (1, 100, 1000)

The below examples shows creating a partitions as follows

Partition 1 : column1 < 1
Partition 2 : column1 >= 1 and column1 < 100
Partition 3 : column1 >= 100 and column1 < 1000
Partition 4 : column1 >= 1000

CREATE PARTITION FUNCTION EmployeePartitionRight (int) AS RANGE RIGHT FOR VALUES (1, 100, 1000)