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
3)Disk constraints in Index Rebuild. Less disk space is required to commit an Index Rebuild
Disable Index and Rebuild Index for Updates on very large tables
ALTER INDEX permissions