Use the script below to disable an index on a SQL Server table

ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME  DISABLE; 

Executing the code will  prevent access to the index. 

If it’s a CLUSTERED INDEX , the data remains intact on the table , but no DML can access the data. Drop the index or rebuild to enable  DML .  Disabling the CLUSTERED INDEX will also disable all NONCLSTERED INDEXES referenced to the table.

If it’s a NONCLUSTERED INDEX, the data is physically deleted.  The index definition  details are retained in the meta data.

 Why disable an index?

1)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

2)       Index troubleshooting

3)       Disk constraints in  Index Rebuild. Less disk space is required to commit an Index Rebuild

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

ALTER INDEX permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)