Microsoft® SQL Server® 2012 Bible

1120

Part VIII: Performance Tuning and Optimization

GO

ALTER DATABASE AdventureWorks

ADD FILEGROUP AdventureWorks_SalesOrderDetail2009Partition

GO

ALTER DATABASE AdventureWorks

ADD FILE

(

NAME = 'AdventureWorks_SalesOrderDetail2009Partition',

FILENAME = 'C:\SQLData\

AdventureWorks_SalesOrderDetail2009Partition.ndf',

SIZE = 10,

MAXSIZE = 120,

FILEGROWTH = 10

)

TO FILEGROUP AdventureWorks_SalesOrderDetail2009Partition

GO

The fi le location specifi ed in this script is specifi c to the author’s machine. You must mod-

ify it before running the script. In the above script you should change the fi le path, which

is bolded in the script, to a valid location on your machine.

Creating the Partition Function

A partition function simply specifi es how the index or table is partitioned. The function

creates a mapping of domains into a set of partitions. When creating a partition function,

you defi ne the number of partitions, the column that will be used to defi ne the boundaries

of partitions, and the range of partition column values for each partition.

In the following example, the function fnOrderYears takes a datetime value. The func-

tion defi nes the boundary values for the ranges of each partition. An important aspect of

boundary values is that you specify only the boundary values between ranges; they don’t

defi ne the upper or lower values for the whole table.

A boundary value can exist only in one partition. The ranges are defi ned as left or right. If

a row has a partition column value that is the same as a boundary value, then SQL Server

needs to know in which partition to put the row.

Left ranges mean that data equal to the boundary is included in the partition to the left of

the boundary. A boundary of '12/31/2011' would create two partitions. The lower parti-

tion would include all data up to and including '12/31/2011', and the right partition

would include any data greater than '12/31/2011'.

Right ranges mean that data equal to the boundary goes into the partition on the right of

the boundary value. To separate at the new year starting 2012, a right range would set the

boundary at '1/1/2012'. Any values less than the boundary go into the left, or lower,

boundary. Any data with a date equal to or later than the boundary goes into the next

partition. These two functions use left and right ranges to create the same result:

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

http://www.it-ebooks.info