Disable / Drop SQL Indexes
Below are couple of points on difference between Disabling an Index / Dropping an Index:
| Disabling an Index | Dropping an Index |
|---|---|
|
Disabling a non clustered index deletes the data rows of the index but keeps its metadata intact. The space is freed on the database. Disabling a clustered index has the effect of taking a table entirely offline. Disabling a clustered index also blocks access to the underlying table data as the data still remains in the table, but is unavailable for DML operations unless you drop or rebuild the index. Disabling an index on a view physically deletes the data associated with the index. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. A disabled index can be dropped. |
On dropping an index, index is physically removed from the database. Dropping an index frees the space taken up by the index and also removes the index definition from the database. Clustered indexes’ table data remains in an heap form. You can’t use Drop Index to remove indexes that result from the creation of a Primary key or unique constraint. |