Microsoft® SQL Server® 2012 Bible

1121

Chapter 49: Partitioning

49

CREATE PARTITION FUNCTION fnOrderYears(DateTime)

AS RANGE LEFT FOR VALUES

('12/31/2005', '12/31/2006', '12/31/2007', '12/31/2008');

or

CREATE PARTITION FUNCTION fnOrderYears(DateTime)

AS RANGE RIGHT FOR VALUES

('1/1/2006', '1/1/2007', '1/1/2008', '1/1/2009');

These functions both create four defi ned boundaries and thus fi ve partitions. This example

uses the function that uses LEFT boundaries.

Three catalog views expose information about partition function: sys.partition_functions, sys

.partition_range_values, and sys.partition_parameters.

Creating Partition Schemes

The partition schema builds on the partition function to specify the physical locations for

the partitions. As previously mentioned, the scheme maps the partitions to the database fi le-

groups. The physical partition tables may all be located in the same fi legroup or spread over

several fi legroups. The fi rst example partition scheme, named psOrderYearsAll, uses the

fnOrderYears partition function and places all the partitions in the Primary fi legroup:

CREATE PARTITION SCHEME psYearsOrderAll

AS PARTITION fnOrderYears

ALL TO ([PRIMARY]);

To place the table partitions in their own fi legroup, omit the ALL keyword and list the fi le-

groups individually. This creates fi ve partitions to match the four boundary values specifi c

in the function:

--Create Partition Scheme

CREATE PARTITION SCHEME psOrderYearsFiles

AS PARTITION pfyearsfnOrderYears

TO(

AdventureWorks_SalesOrderDetail2005Partition,

AdventureWorks_SalesOrderDetail2006Partition,

AdventureWorks_SalesOrderDetail2007Partition,

AdventureWorks_SalesOrderDetail2008Partition,

AdventureWorks_SalesOrderDetail2009Partition

)

The partition functions and schemes must be created using T-SQL code, but after they’ve

been created, you can view them in Management Studio’s Object Explorer under the data-

base Storage node.

c49.indd 1121c49.indd 1121 7/31/2012 10:24:23 AM7/31/2012 10:24:23 AM

http://www.it-ebooks.info