Microsoft® SQL Server® 2012 Bible

1124

Part VIII: Performance Tuning and Optimization

Altering Partition Tables

For partition tables to be updated to keep up with changing data, and to enable the per-

formance testing of various partition schemes, they are easily modifi ed. Even though the

commands are simple, modifying the design of partition tables never executes quickly, as

you can imagine.

Merging Partitions

Merge and split surgically modify the table partition design. The ALTER PARTITION.. .MERGE

RANGE command effectively removes one of the boundaries from the partition function and

merges two partitions. For example, to remove the boundary between 2007 and 2008 in the

fnOrderYears partition function, and combine the data from 2007 and 2008 into a single par-

tition, use the following ALTER command:

ALTER PARTITION FUNCTION pfyearsfnOrderYears()

MERGE RANGE ('12/31/2007');

Following the merge operation, the previous count-rows-per-partition query now returns

three partitions, and scripting the partition function from Object Explorer creates a script

with three boundaries in the partition function code. The 2007 partition has been removed

or merged with the 2008 partition.

If multiple tables share the same partition scheme and partition function being modifi ed, then multiple tables will be

affected by these changes.

Splitting Partitions

To split an existing single partition, the fi rst step is to designate the next

fi legroup to be used by the partition scheme. You can do this by using the

ALTER PARTITION.. .NEXT USED command. If you specify too many fi legroups

when creating a scheme, you get a message that the next fi legroup used is the extra

fi le group you specifi ed. Then you can modify the partition function to specify the

new boundary using the ALTER PARTITION.. .SPLIT RANGE command to insert a

new boundary into the partition function. The ALTER FUNCTION command actually

performs the work.

This example segments the 2007–2008 sales order data into two partitions. The new parti-

tion includes only data for 2007:

ALTER PARTITION SCHEME psOrderYearsFiles

NEXT USED [AdventureWorks_SalesOrderDetail2007Partition];

ALTER PARTITION FUNCTION pfyearsfnOrderYears()

SPLIT RANGE ('12/31/2007');

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

http://www.it-ebooks.info