Disabling Indexes
Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in metadata and index statistics are kept on nonclustered indexes. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for 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.
One or more indexes might be disabled in the following circumstances:
The SQL Server Database Engine automatically disables the index during an upgrade of SQL Server.
You manually disable the index by using ALTER INDEX.
SQL Server Upgrade Disables an Index
During an upgrade to a new release of or service pack for SQL Server, the Database Engine automatically identifies and disables an index, including indexes on views, when the index or view definition contains one of the following:
An expression for which the Database Engine can no longer guarantee the data integrity of the index.
For example, assume an index uses a system function and that function has been changed in the upgrade in such a way that it now returns a different result. The Database Engine will disable the index during the upgrade because it may contain data that is not valid after the upgrade.
A collation that has been changed as part of the upgrade in such a way that the index is no longer sorted correctly.
When an index is disabled during the upgrade process, a warning message displays the index name and any associated constraint names so that you can rebuild them after the upgrade finishes. Rebuilding the index and enabling any constraints will correct the data invalidated during the upgrade.
A CHECK constraint may be disabled if the definition contains an expression for which the Database Engine can no longer guarantee the integrity of the data. To enable the constraint, use the ALTER TABLE CHECK statement.
Using ALTER INDEX to Disable an Index
You can manually disable an index at any time by using the ALTER INDEX DISABLE statement.
Note
If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication. For more information, see Publishing Data and Database Objects.
You may want to disable an index to perform the following:
Correcting a disk I/O error (823 or 824 error) on an index page and then rebuilding the index later.
Temporarily removing the index for troubleshooting purposes.
Rebuilding nonclustered indexes.
When a nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. No additional space is required except for temporary disk space for sorting; this is typically 20 percent of the index size.
If disk space is limited, it may be helpful to disable the nonclustered index before rebuilding it. For example, you have a stored procedure that rebuilds all nonclustered indexes in one or more tables. By disabling these indexes first in a separate transaction from the rebuild operation, you can significantly reduce the amount of temporary disk space required to rebuild them.
To disable an index