Efficiently Rebuild SQL Server Clustered Indexes with DROP_EXISTING
By: Armando Prato | Updated: 2007-10-30 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Fragmentation and Index Maintenance
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 |
|
|
Limitations to using DROP_EXISTING option: | |
SQL Server 2000 | SQL Server 2005 |
|
|
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:
- Check out the following related tips:
Related Articles
Popular Articles
About the author

View all my tips
Article Last Updated: 2007-10-30