Rebuilding a SQL Server Index

When you perform any data modifications using the INSERT, UPDATE, or DELETE statement, data fragmentation can occur. If these data are indexed, index fragmentation can occur as well, and the information in the index can get scattered on different physical pages. Fragmented index data can cause the Database Engine to perform additional data reads, which decreases the overall performance of the system. In such a case, you have to rebuild all fragmented indexes.

There are two ways in which you can rebuild an index:

• Use the REBUILD option of the ALTER INDEX statement
• Use the DROP_EXISTING option of the CREATE INDEX statement

With the REBUILD option, you can rebuild an index. If you specify ALL instead of an index name, all indices of the table will be rebuilt. (By allowing indices to be rebuilt dynamically, you don’t have to drop and re-create them.)

The DROP_EXISTING option of the CREATE INDEX statement allows you to enhance performance when re-creating a clustered index on a table that also has nonclustered indices. It specifies that the existing clustered or nonclustered index should be dropped and the specified index rebuilt. As you already know, each nonclustered index in a clustered table contains in its leaf nodes the corresponding values of the table’s clustered index. For this reason, all nonclustered indices must be rebuilt when a table’s clustered index is dropped. Using the DROP_EXISTING option, you can prevent the nonclustered indices from being rebuilt twice.

NOTE

The DROP_EXISTING option is more powerful than REBUILD, because it is more flexible and offers several options, such as changing the columns that make up the index and changing a nonclustered index to a clustered one.