Rebuilding an Index

Creating and Maintaining Databases

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. This can improve disk performance by reducing the number of page reads required to obtain the requested data

In Microsoft® SQL Server™ 2000, rebuilding an index using the DROP_EXISTING clause of the CREATE INDEX statement 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 cause all the nonclustered indexes to be deleted and re-created twice. This occurs once when you delete the clustered index, and a second time 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.

DBCC DBREINDEX is also 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.

To delete an index

Transact-SQL