solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL Sentry Performance Advisor for SQL Server

SQL compliance manager

SQL safe backup

SQL Monitor

SQL diagnostic manager

Efficiently Rebuild SQL Server Clustered Indexes with DROP_EXISTING

By: Armando Prato | Read Comments | Print

Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5.

Related Tips: More

Problem
When re-building a table’s clustered index using a DROP INDEX command followed by a CREATE INDEX command, if a table has non-clustered indexes then all of non-clustered indexes on the table may be automatically rebuilt twice by the SQL Server engine. Once when the clustered index is dropped and a second time when the index is created again, depending on the new clustered index definition. Is there some way we can eliminate the system resource overhead of rebuilding all of the table’s non-clustered indexes twice?

Solution
To efficiently rebuild a clustered index, the CREATE INDEX command provides the DROP_EXISTING option. This option can rebuild the clustered index in a single atomic step and re-creates the non-clustered indexes of the table only once depending on the index definition. This sets each index leaf level page pointer to the clustered index key while eliminating the overhead of setting the index page leaf pointer to a direct address first. On very large tables, the resource savings can be immense.

As a point of reference, each index leaf level page of a non-clustered index contains a pointer to where the data requested resides. This pointer can be one of two values. For tables without a clustered index, this pointer is a direct address to a data page. For tables with a clustered index, this value is a pointer to the clustered index key related to the data. When a table’s clustered index is dropped using a DROP command and then re-added using a CREATE command, each non-clustered index on the table can be re-created twice. When the DROP is initiated, all non-clustered indexes on the table are re-created to reset its index page pointers to directly address a data page. When the CREATE is then executed, the non-clustered indexes are re-created again, this time with a pointer set back to a clustered index key.

As is the case with many features related to SQL Server 2005, they have been enhanced over the SQL Server 2000 functionality. Let's dive into some of the small details related to the DROP_EXISTING option of the CREATE INDEX command between SQL Server 2000 and 2005:

The DROP_EXISTING option can also be used in the following scenarios:
SQL Server 2000	SQL Server 2005
Rebuild a preexisting clustered index Rebuild a preexisting non-clustered index Rebuild a non-clustered index as a clustered index if a clustered index is not already defined Rebuild an index and add or remove the keys Rebuild an index to add or remove uniqueness	Rebuild a preexisting clustered index Rebuild a preexisting non-clustered index Rebuild a non-clustered index as a clustered index if a clustered index is not already defined Rebuild an index and add or remove the keys Rebuild an index to add or remove uniqueness Rebuild an index to modify options Rebuild an index to modify sort order Rebuild an index to modify the partition scheme or filegroup If an index enforces a PRIMARY KEY or UNIQUE constraint and the definition is not alerted, then the index can be dropped and re-created If the index definition has the same name, key and partition columns, uniqueness attributes and sort order, then the non-clustered indexes are not rebuilt
Limitations to using DROP_EXISTING option:
SQL Server 2000	SQL Server 2005
The index must already exist; using this option will not allow creation of an index from scratch You cannot use it to rebuild a clustered index to be non-clustered If rebuilding an index tied to a constraint, you cannot change the index’s definition	The index must already exist; using this option will not allow creation of an index from scratch You cannot use it to rebuild a clustered index to be non-clustered If rebuilding an index tied to a constraint, you cannot change the index’s definition The non-clustered indexes will always remain in the original file group or partition scheme with the original partition functions even if the clustered index is rebuilt to another file group or partition If an index enforces a PRIMARY KEY or UNIQUE constraint and the definition is alerted, then drop the constraint and add the constraint with the new definition

Here is an example of the DROP_EXISTING option of the CREATE INDEX command in action:

Sample DROP_EXISTING Code
SQL Server 2000	SQL Server 2005
CREATE CLUSTERED INDEX au_id_clidx ON Authors (au_id) WITH DROP_EXISTING GO	CREATE CLUSTERED INDEX au_id_clidx ON dbo.Authors (au_id) WITH (DROP_EXISTING = ON); GO

Next Steps

Examine your scripts to see where you can benefit from using the DROP_EXISTING option over DROP/CREATE statements.
Read more about CREATE INDEX and its options:
- SQL Server 2000 Books Online
- SQL Server 2005 Books Online
Check out the following related tips:

Related Tips: More | Become a paid author

Last Update: 10/30/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial!

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Demystify TempDB Performance and Manageability