Microsoft® SQL Server® 2012 Bible

1128

Part VIII: Performance Tuning and Optimization

Rolling partitions are useful for time-based partition functions such as partitioning a year

of data into months. Each month, the rolling partition expands for a new month. To build a

13-month rolling partition, perform these steps each month:

1. Add a new boundary.


2. Point the boundary to the next used fi legroup.


3. Merge the oldest two partitions to keep all the data.

Switching tables into and out of partitions can enhance the rolling partition designs by

switching in fully populated staging tables and switching out the tables into an archive

location.

Indexing Partitioned Tables

Large tables mean large indexes, so nonclustered indexes can be optionally partitioned.

Creating Partitioned Indexes

Partitioned nonclustered indexes must include the column used by the partition function

in the index and must be created using the same ON clause as the partitioned clustered

index:

CREATE INDEX SalesOrderDetail_ProductID

ON SalesOrderDetail (ProductID, OrderDate)ON psOrderYearsFiles

(OrderDate);

Maintaining Partitioned Indexes

One of the advantages of partitioned indexes is that they can be individually maintained.

The following example rebuilds the newly added fi fth partition:

ALTER INDEX SalesOrderDetail_ProductID

ON dbo.SalesOrderDetail

REBUILD

PARTITION = 5

Removing Partitioning

To remove the partitioning of any table, drop the clustered index and add a new clustered

index without the partitioning ON clause. When dropping the clustered index, you must

add the MOVE TO option to actually consolidate the data onto the specifi ed fi legroup, thus

removing the partitioning from the table:

DROP INDEX CIX_SalesOrderDetail_OrderDate ON dbo.SalesOrderDetail

WITH (MOVE TO [PRIMARY]);

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

http://www.it-ebooks.info