Partition switching and indexing

With SQL Server 2014, it is possible for individual partitions of partitioned tables to be rebuilt.

In the following code example, we will rebuild a single partition, that is, partition number 5 of the IX_GETEST partitioned index on the dbo.t1 table:

ALTER INDEX IX_GETEST
ON dbo.t1
REBUILD Partition = 5

The ONLINE = ON option can also be used and now contains a further switch WAIT_AT_LOW_PRIORITY option that will allow you as a DBA to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option will also allow the DBA to configure the termination of blocking processes related to the rebuild statement. This can be used in combination with the REBUILD Partition option.