Sunday, November 29, 2009

FAQ : What is Partition Scheme in SQL Server?

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
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);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

No comments:

 
Locations of visitors to this page