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