Menu

In addition to creating indexes in sql server, database developer also need to maintain them to ensure their continued optimal performance. The common index maintenance tasks include disabling, enabling, renaming, and dropping an index. As a database developer, you need to regularly monitor the performance of the index and optimize it.

Disabling Indexes

When an index is disabled, the user is not able to access the index. If a clustered index is disabled then the table data is not accessible to the user. However, the data still remains in the table, but is unavailable for Data Modification Language (DML) operations until the index is dropped or rebuilt.

To rebuild and enable a disabled index, use the ALTER INDEX REBUILDstatement or the CREATE INDEX WITH DROP_EXISTING statement.

The following query disables a non-clustered index, IX_EmployeeID, on the Employee table.

ALTER INDEX IX_EmployeeIDON Employee DISABLE

Enabling Indexes

After an index is disabled, it remains in the disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it through one of the following methods:

Using the ALTER INDEX statement with the REBUILD clause

Using the CREATE INDEX statement with the DROP_EXISTING clause

Using the DBCC DBREINDEX

By using one of the preceding statements, the index is rebuilt and the index status is set to enable. You can rebuild a disabled clustered index, when the ONLINE option is set to ON.

Renaming Indexes

You can rename the current index with the help of the sp_rename system stored procedure.
The following statement renames the IX_JobCandidate_EmployeeID index on the JobCandidate table to IX_EmployeeID.