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.
To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement 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_EmployeeID
ON Employee DISABLE
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.
The following statement renames the IX_JobCandidate_EmployeeID index on the JobCandidate table to IX_EmployeeID.
EXEC sp_rename
‘HumanResources.JobCandidate.IX_JobCandidate_EmployeeID’,
‘IX_EmployeeID’, ‘index’
The following statement drops the IDX_Employee_ManagerID index on the Employee table:
DROP INDEX IDX_Employee_ManagerID
ON Employee
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 REBUILD statement 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_EmployeeID
ON 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.
EXEC sp_rename
‘HumanResources.JobCandidate.IX_JobCandidate_EmployeeID’,
‘IX_EmployeeID’, ‘index’
Dropping Indexes
When you no longer need an index, you can remove it from a database. You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint.The following statement drops the IDX_Employee_ManagerID index on the Employee table:
DROP INDEX IDX_Employee_ManagerID
ON Employee