Guidelines for Disabling Indexes and Constraints

Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data. The SQL Server Database Engine may automatically disable an index during a upgrade of SQL Server, or you can manually disable an index. For more information, see Disabling Indexes.

Any index type can be disabled. When an index is disabled, the following rules apply:

• If the index is unique, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are disabled. The user disabling the index must have ALTER permissions on these tables or the ALTER INDEX DISABLE statement fails. If the index is clustered, all incoming and outgoing FOREIGN KEY constraints on the underlying table are disabled.

  The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.

• The index is not maintained while it is disabled.

• The query optimizer does not consider the index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.

• You cannot create an index that uses the same name as an existing disabled index, because the index definition still exists in metadata.

• A disabled index can be dropped.

Disabling Nonclustered Indexes

Disabling a nonclustered index physically deletes the index data. However, the index definition remains in metadata. The following additional guidelines apply to disabling nonclustered indexes:

• Statistics on the index remain in place and are automatically updated as needed.

• 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. For more information, see Guidelines for Enabling Indexes and Constraints.

• Creating a new clustered index enables previously disabled nonclustered indexes. For more information, see Guidelines for Enabling Indexes and Constraints.

Disabling Clustered Indexes

The following additional guidelines apply to disabling clustered indexes:

• The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index. This means the following:

  • These operations will fail: SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (on the index), and ALTER TABLE statements that modify table columns or constraints.

  • These operations will succeed: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE, and DROP TABLE.

  • Nonclustered indexes cannot be created while the clustered index is disabled.

• Existing nonclustered indexes and XML indexes associated with the table are automatically disabled and cannot be accessed.

• All clustered and nonclustered indexes on views that reference the table are disabled. These indexes must be rebuilt just as those on the referenced table.

Disabling Constraints

These additional guidelines apply to disabling PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints:

• PRIMARY KEY and UNIQUE constraints are disabled by disabling the associated index by using the ALTER INDEX DISABLE statement.

• When a PRIMARY KEY constraint is disabled, all associated FOREIGN KEY constraints are also disabled. This is the equivalent of setting the NOCHECK CONSTRAINT option on the constraint.

• You must have ALTER or CONTROL permissions on the referenced tables.

• If a CASCADE UPDATE or DELETE action is declared on a foreign key reference and that reference is disabled, any updates or delete statements that would cause the constraint to propagate the modification to the referencing table fail.

• Duplicate values may be accidentally added to a table while the PRIMARY KEY or UNIQUE index is disabled or, in a SQL Server upgrade, by the change that caused the index to become disabled. You must manually correct the duplicate rows before the index can be successfully enabled. The following resolutions are possible:

  • Manually remove or change the duplicate values.

  • If the UNIQUE index was not created as the result of creating a UNIQUE constraint, use CREATE INDEX WITH DROP_EXISTING to re-create the index without specifying UNIQUE.

  • If the index was created as a byproduct of a PRIMARY KEY or UNIQUE constraint, you must drop the constraint. The index is then dropped. For a PRIMARY KEY constraint, any FOREIGN KEY constraints must also be dropped.

• FOREIGN KEY and CHECK constraints that are disabled are marked is_not_trusted.These are viewable in the sys.check_constraints and sys.foreign_keys catalog views. This means that the constraint is no longer being verified by the system for all rows of the table. Even when you re-enable the constraint, it will not reverify the existing rows against the table unless you specify the WITH CHECK option of ALTER TABLE. Specifying WITH CHECK marks the constraint as trusted again.

  The following example disables a constraint that limits the salaries accepted in the data. NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow for an insert that would typically violate the constraint. The WITH CHECK CHECK CONSTRAINT re-enables the constraint, and also validates the existing data against the re-enabled constraint.

  CREATE TABLE cnst_example 
  (id INT NOT NULL,
      name VARCHAR(10) NOT NULL,
      salary MONEY NOT NULL
      CONSTRAINT salary_cap CHECK (salary < 100000);
  )
  
  -- Disable the constraint.
  ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;
  
  -- Reenable the constraint.
  ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;
  

Disabling Indexes on Views

Disabling a clustered index on a view physically deletes the index data. The following additional guidelines apply to disabling indexes on views:

• Disabling a clustered index on a view does not prevent modifications to the underlying table.

• Disabling a clustered index on a view also disables any nonclustered indexes on that view.

• The index data rows for the clustered and nonclustered indexes are deleted. However, the view and index definitions remain in metadata and can be re-created by rebuilding the index or indexes.

• 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.

• Rebuilding the clustered index on a view does not automatically enable nonclustered indexes on the view.

• The nonclustered indexes must be manually enabled by rebuilding them after rebuilding the clustered index.

Performing Index Operations Online on Disabled Indexes

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. For more information about online index operations, see Performing Index Operations Online.

Statistics on Disabled Indexes

The following restrictions apply to index statistics when the index is disabled:

• The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.

• 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.

• sp_autostats fails when the specified table has a disabled clustered index.

• sp_updatestats does not update statistics on disabled clustered indexes.

• sp_createstats creates statistics on columns that may be leading columns of a disabled index. When indexonly is specified, statistics are not created on a column in a disabled index unless that column is also used in another enabled index.

DBCC Commands

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.

Viewing the Status of a Disabled Index

When an index or PRIMARY KEY or UNIQUE constraint is disabled, a warning message that lists all affected indexes and FOREIGN KEY or CHECK constraints is displayed. Also, you can view the disabled status of an index in the sys.indexes catalog view or by using the INDEXPROPERTY function. You can view the disabled status of FOREIGN KEY and CHECK constraints in the sys.foreign_keys and sys.check_constraints catalog views, respectively. For more information, see Viewing Index Information.

Examples

The following example disables a nonclustered index on the Employee table.

USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode 
ON HumanResources.Employee DISABLE;

See Also

Reference

ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)

DBCC CHECKDB (Transact-SQL)

DBCC INDEXDEFRAG (Transact-SQL)

DBCC DBREINDEX (Transact-SQL)

INDEXPROPERTY (Transact-SQL)

sys.check_constraints (Transact-SQL)

sys.foreign_keys (Transact-SQL)

sys.indexes (Transact-SQL)

Concepts

Disabling Indexes

Guidelines for Enabling Indexes and Constraints