SQL SERVER – Disable Index – Enable Index – ALTER Index

There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again.

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

Disabling non-clustered index:

 

Whenever a non-clustered index is disabled, only the access to index is disabled. Optimizer will ignore the disabled index even though your query has the perfect filter criteria in which an index can be used. Also, whenever a non-clustered index or index on a view is disabled, index data physically gets deleted.

 

Disabling clustered index:

 

Whenever we disable a clustered index, it disables all the non-clustered indexes of the table as well. Once clustered index is disabled, user can not access the the underlying table data but index definition remains in the system catalog. Another important point to note is, even though user cannot access the data, data still remains in the B-Tree until index is dropped or rebuilt but it is not maintained.

 

Enabling index:

 

Indexes can be enabled using ‘ALTER INDEX REBUILD’ command or ‘CREATE INDEX WITH DROP_EXISTING’ command. We can enable individual index on the table or we can enable all the indexes on the table using ‘ALL’ clause. Again one thing to keep in mind is that, enabling clustered index does not enable all non-clustered indexes. You have to enable them individually.