The Database Engine is one of a few database systems that support the ALTER INDEX statement. This statement can be used for index maintenance activities. The syntax of the ALTER INDEX statement is very similar to the syntax of the CREATE INDEX statement. In other words, this statement allows you to change the settings for the options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY, and STATISTICS_NORECOMPUTE, previously described in relation to the CREATE INDEX statement.
In addition to the preceding options, the ALTER INDEX statement supports three other activities:
- Rebuilding an index using the REBUILD option
- Reorganizing leaf index pages using the REORGANIZE option
- Disabling an index using the DISABLE option
The following subsections discuss these options.
Rebuilding an 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.
Reorganizing Leaf Index Pages
The REORGANIZE option of the ALTER INDEX statement specifies that the leaf pages of the corresponding index structure will be reorganized so that the physical order of the pages matches the left-to-right logical order of the leaf nodes. Therefore, this option removes some of the fragmentation from an index, thus improving performance.
Disabling an Index
The DISABLE option disables an existing index. Each disabled index is unavailable for use until you enable it again. Note that a disabled index won’t be maintained as changes to the corresponding data are made. For this reason, indices must be completely rebuilt if you want to use them again. To enable a disabled index, use the REBUILD option of the ALTER TABLE statement.
Removing and Renaming Indices
The DROP INDEX statement removes one or more existing indices from the current database. Note that removing the clustered index of a table can be a very resourceintensive operation, because all nonclustered indices will have to be rebuilt. (All the nonclustered indices use the index key of the clustered index as a pointer in their leaf index pages.) Example 10.4 shows how the i_empno index can be dropped.
Remove the index created in Example 10.1:
DROP INDEX i_empno ON employee;
The DROP INDEX statement has an additional option, MOVE TO, which is analogous to the ON option of CREATE INDEX. In other words, you can use this option to specify a location to which to move the data rows that are currently in the leaf pages of the clustered index. The data is moved to the new location in the form of a heap. You can specify either a default or named file group as the new location.
The sp_rename system procedure, which is discussed in Chapter “Data Definition Language“, can be used to rename indices.