Microsoft® SQL Server 2012 Unleashed

Disabling Indexes

Another feature available in SQL Server 2012 is the capability to set an index as disabled. When an index is disabled, the definition of the index is maintained in the system catalogs, but the index itself contains no index key rows. Disabling an index prevents use of the index by any queries against the table. Disabling a clustered index also prevents access to the underlying table data.

You can manually disable an index at any time by using the ALTER INDEX DISABLE statement:

Click here to view code image

ALTER INDEX Product_number_index ON Production.Product DISABLE

The reasons you might want to disable an index include the following:

Correcting a disk I/O or allocation error on an index page and then rebuilding the index ...