MCTS 70-433 Partition Functions and Schemes

I have worked with partitioned tablespaces, tables and indexes with Oracle and so am familar with concepts, which are broadly similar in SQLServer. However the physical implementation in SQLServer has it's own syntax and terminology, here "partition functions" define range partition which map to "partition schemes"

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.

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

http://msdn.microsoft.com/en-us/library/ms179854.aspx