Disable an Index

The option to disable an index became available in v2005.  There are multiple reasons for disabling an index, but I only do it for a large bulk upload, or to determine whether the index is necessary, and even useful at all.  I want to show you quickly how to disable and re-enable an index, but there are some key points to be aware of:

• Disabling a clustered index on a view will DELETE the data.  ouch!
• Disable a clustered index, the data is still present, but it cannot be used.  This is because the leaf level of the clustered index is the actual table data itself. 
• Disable a non-clustered index, the index actually remains on disk, and there is no physical disk change at all.  
• A primary key cannot be disabled if the associated table is published within transactional replication.  (Makes sense.)
• To ENABLE the index, it has to be rebuilt.  This is because when we DISABLE an index, the definition remains in the meta data, but the index updates are not performed.  It has to be rebuilt, and brought current, before it can be enabled.

Indexes can be disabled and enabled within the SSMS gui, but this is the tSQL method:

  DISABLE Index:
ALTER INDEX IndexName ON dbo.TableName DISABLE;

 ENABLE Index:

   ALTER INDEX IndexName ON dbo.TableName REBUILD;

OR

  CREATE NONCLUSTERED INDEX IndexName ON dbo.TableName 

  (IndexKeyName ASC)

  WITH (DROP_EXISTING = ON, FILLFACTOR = 80) ON [PRIMARY];

This is just a quick reference, but I encourage you to also read these guidelines:

Guidelines for Disabling Indexes and Constraints