Use CREATE INDEX's DROP_EXISTING Clause When Recreating a Clustered Index

The internal structure of a nonclustered index in SQL Server varies based on whether the table also has a clustered index. Tables that have a clustered index are clustered tables, and tables that don't have a clustered index are heap tables. Nonclustered indexes based on a heap table contain a row ID (RID) that lets SQL Server find a data page from the leaf level of the nonclustered index. This process generally improves performance because it dramatically reduces the overhead associated with maintaining a nonclustered index when you update data. (For more information about nonclustered indexes, see Kalen Delaney's articles "Time for a Tune-Up," August 2001, InstantDoc ID 21480, and "The Big Cover-Up," September 2001, InstantDoc ID 21729).

However, because the nonclustered index contains the clustering key, if you drop a clustered index on a table that has nonclustered indexes, SQL Server must also drop the nonclustered indexes and recreate them by using a RID rather than the clustering key as a record locator. SQL Server must drop and recreate the nonclustered index once again if you add back a clustered index because the nonclustered index will need to shift from using a RID back to using the clustering key. This dropping-and-recreating process can take a tremendous amount of time and I/O and CPU resources when you're dealing with large tables.

Changing your clustered indexes by using the CREATE INDEX statement's DROP_EXISTING clause is faster. The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place. (Note that you can use DBCC DBREINDEX to rebuild existing indexes because it won't cause SQL Server to rebuild a nonclustered index. But you can't use DBCC DBREINDEX to change the columns in an existing index.) With DROP_EXISTING, you save one complete cycle of dropping and recreating nonclustered indexes. Additionally, SQL Server won't rebuild the nonclustered index at all if the clustered index key doesn't change and is defined as UNIQUE, which isn't an obvious performance benefit of defining a clustered index as UNIQUE. Using the DROP_EXISTING clause can be a huge time-saver when you need to change the clustered index on a table that also has nonclustered indexes.