0

I'm aware that there are substantial differences between Dropping and Disabling a CLUSTERED Index, but I want to know about NON-CLUSTERED indexes.

And I'm aware that Disabling an Index retains the Metadata of the index definition, making it easier and lower maintenance to reinstate the Index afterwards.

But from a straight "Performance of INSERTs, UPDATEs and DELETEs" point-of-view ...

Is there any performance difference between having Disabled a Non-Clustered Index, and having dropped it entirely. Either during the table edits, or for the CREATE vs the REBUILD afterwards?

2
  • Potentially helpful Jes Schultz article (on Brent Ozar), What’s Better: Disabling vs. Dropping Indexes?
    – J.D.
    May 7 at 11:20
  • @J.D. Yeah already found that article; it doesn't say anything at all about the performance differences (if any). It just gives you what the syntaxes are and mentions the metadata.
    – Brondahl
    May 7 at 14:25
2

As documented:

The query optimizer does not consider the disabled index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.

The index is completely ignored for querying

The index is not maintained while it is disabled.

So it doesn't appear in inserts, updates and deletes.

You cannot create an index that uses the same name as an existing disabled index.

Not documented, but you cannot drop columns that the index depends on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.