How to rebuild an index in a SQL Server 2000 database

When you create an index in a database, the index information used by the query is stored in the index page. A continuous index page is linked by a pointer from one page to the next. When changes to the data affect the index, the information in the index may be dispersed in the database. Rebuilding an index can rearrange the storage of index data (also including table data for clustered indexes) 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, if you want to re-create the index with one step, and you do not want to delete the old index and re-create the same index, using the DROP_EXISTING clause of the CREATE index statement can improve efficiency. This benefit applies both to clustered indexes and to nonclustered indexes.

Rebuilding a clustered index by deleting the old index and recreating the same index is an expensive method because all level two indexes use a clustered key to point to the data row. If you simply delete the clustered index and re-create it, all nonclustered indexes are deleted and recreated two times. This situation can occur once the clustered index is deleted and the index is rebuilt again. This expensive approach can be avoided by recreating the index in one step. When you re-create the index in one step, you tell SQL Server to rearrange the existing indexes, avoiding the unnecessary work of deleting and recreating nonclustered indexes. Another important advantage of this approach is that you can use the data sort order in an existing index to avoid reordering the data. This is useful for both clustered and nonclustered indexes, which can significantly reduce the cost of rebuilding the index. Additionally, by using the DBCC dbreindex statement, SQL Server allows one or more indexes to be rebuilt (in one step) on a table without having to rebuild each index separately.

DBCC Dbreindex can also be used to reconstruct indexes that perform PRIMARY key or UNIQUE constraints, without having to delete and create these constraints (because this constraint must be removed before the index can be deleted for an index created to perform a PRIMARY key or a unique constraint). For example, you might need to rebuild an index on the PRIMARY KEY constraint to reconstruct the given fill factor for that index.