CREATE PARTITION SCHEME (Transact-SQL)
Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups. The number and domain of the partitions of a partitioned table or index are determined in a partition function. A partition function must first be created in a CREATE PARTITION FUNCTION statement before creating a partition scheme.
The following permissions can be used to execute CREATE PARTITION SCHEME:
-
ALTER ANY DATASPACE permission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.
-
CONTROL or ALTER permission on the database in which the partition scheme is being created.
-
CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition scheme is being created.
A. Creating a partition scheme that maps each partition to a different filegroup
The following example creates a partition function to partition a table or index into four partitions. A partition scheme is then created that specifies the filegroups to hold each one of the four partitions. This example assumes the filegroups already exist in the database.
CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg);
The partitions of a table that uses partition function myRangePF1 on partitioning column col1 would be assigned as shown in the following table.
Filegroup | test1fg | test2fg | test3fg | test4fg |
Partition | 1 | 2 | 3 | 4 |
Values | col1 <= 1 | col1 > 1 AND col1 <= 100 | col1 > 100 AND col1 <= 1000 | col1 > 1000 |
B. Creating a partition scheme that maps multiple partitions to the same filegroup
If all the partitions map to the same filegroup, use the ALL keyword. But if multiple, but not all, partitions are mapped to the same filegroup, the filegroup name must be repeated, as shown in the following example.
CREATE PARTITION FUNCTION myRangePF2 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS2 AS PARTITION myRangePF2 TO ( test1fg, test1fg, test1fg, test2fg );
The partitions of a table that uses partition function myRangePF2 on partitioning column col1 would be assigned as shown in the following table.
Filegroup | test1fg | test1fg | test1fg | test2fg |
Partition | 1 | 2 | 3 | 4 |
Values | col1 <= 1 | col1 > 1 AND col1 <= 100 | col1 > 100 AND col1 <= 1000 | col1 > 1000 |
C. Creating a partition scheme that maps all partitions to the same filegroup
The following example creates the same partition function as in the previous examples, and a partition scheme is created that maps all partitions to the same filegroup.
CREATE PARTITION FUNCTION myRangePF3 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS3 AS PARTITION myRangePF3 ALL TO ( test1fg );
D. Creating a partition scheme that specifies a 'NEXT USED' filegroup
The following example creates the same partition function as in the previous examples, and a partition scheme is created that lists more filegroups than there are partitions created by the associated partition function.
CREATE PARTITION FUNCTION myRangePF4 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS4 AS PARTITION myRangePF4 TO (test1fg, test2fg, test3fg, test4fg, test5fg)
Executing the statement returns the following message.
Partition scheme 'myRangePS4' has been created successfully. 'test5fg' is marked as the next used filegroup in partition scheme 'myRangePS4'.
If partition function myRangePF4 is changed to add a partition, filegroup test5fg receives the newly created partition.
