MS SQL Server 2012 - DBA Articles

Disable Index

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 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 data manipulation language (DML) operations until the index is dropped or rebuilt.
The following example describes to disable existed IX_Employee index on Employee Table.

-- Example 160 --

USE EBusiness

-- Disable Nonclustered Index on Employee Table --

ALTER INDEX IX_Employee_Nonclustered ON dbo.Employee

DISABLE

Query Output Screen

Limitations and Restrictions
01. The index is not maintained while it is disabled.
02. The query optimizer does not consider the disabled index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.
03. You cannot create an index that uses the same name as an existing disabled index.
04. A disabled index can be dropped.
05. When disabling a unique index, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled. When disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
06. Nonclustered indexes are automatically disabled when the associated clustered index is disabled. They cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped. Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
07. The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table, except for disabled indexes on views. Indexes on views must be enabled in a separate ALTER INDEX ALL REBUILD statement.
08. Disabling a clustered index on a table also disables all clustered and nonclustered indexes on views that reference that table. These indexes must be rebuilt just as those on the referenced table.
09. The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.
10. You can rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. However, you must always rebuild a disabled clustered index offline if you use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement.
11. The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.
12. The AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled and the following conditions exist:
           AUTO_CREATE_STATISTICS is set to ON
           There are no existing statistics for the column.
           Statistics are required during query optimization.
13. If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table; instead, the statement reports that the clustered index is disabled. DBCC INDEXDEFRAG cannot be used to defragment a disabled index; the statement fails with an error message. You can use DBCC DBREINDEX to rebuild a disabled index.
14. Creating a new clustered index enables previously disabled nonclustered indexes.

* * * * *

Your Comment To AUTHOR