Rebuilding an Index

When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation.

Rebuilding an index using the DROP_EXISTING clause of the CREATE INDEX statement in Microsoft® SQL Server™ can be efficient if you re-create the index in a single step rather than delete the old index and then create the same index again. This is a benefit for both clustered and nonclustered indexes.

Rebuilding a clustered index by deleting the old index and then re-creating the same index again is expensive because all the secondary indexes use the clustering key to point to the data rows. If you simply delete the clustered index and re-create it, you will cause all the nonclustered indexes to be deleted and re-created twice: once when you delete the clustered index, again when you re-create it. You avoid this expense by re-creating the index in one step. Re-creating the index in a single step tells SQL Server that you are reorganizing an existing index and avoids the unnecessary work of deleting and re-creating nonclustered indexes. This method also has the significant advantage of using the sorted order of the data in the existing index, thus avoiding the need to sort the data again. This is useful for both clustered and nonclustered indexes and significantly reduces the cost of rebuilding an index. Additionally, SQL Server allows you to rebuild (in one step) one or more indexes on a table by using the DBCC DBREINDEX statement, without having to rebuild each index separately.

Both of these options are useful to rebuild indexes enforcing PRIMARY KEY or UNIQUE constraints without having to delete and re-create the constraints (because an index created to enforce a PRIMARY KEY or UNIQUE constraint cannot be deleted without deleting the constraint first). For example, you may want to rebuild an index on a PRIMARY KEY constraint to reestablish a given fill factor for the index or to reorganize the table using a clustered index.

To delete an index

To create an index on an existing table

To re-create an index in one step

To rebuild one or more indexes on a table

To modify an index

See Also