As I was going through the forums and the #sqlhelp Twitter feed yesterday, I came across a very interesting question:

Is there a way to verify whether an index is disabled in SSMS?

The short and simple answer is: No direct indicator exists in the SQL Server Management Studio (SSMS) to indicate whether an index is enabled or disabled.

To cross-check, I used the disabled index scripts (refer Further Reading Section below) to disable a non-clustered and the clustered index on the Sales.Store table of the AdventureWorks2012 sample database.

The Index Properties window confirms that the indexes are indeed disabled.

This is what the Object Explorer looks like: As can be seen from the screenshot below, the icons for the indexes are the same – before and after the indexes were disabled.

Even the Object Explorer Details window does not show any indication and/or column to indicate that the indexes are disabled

This is one of the many cases where T-SQL scripts prove to be way more powerful than the SSMS UI. While I do not know the real reason why this particular discrepancy exists, I believe it is for performance reasons (the more meta-data that is accessed and refreshed on the UI, the more resources it would take resulting into an overall decline in performance).

Further Reading

• ALTER INDEX – Enable & Disable Indexes [Link]
• Disable Clustered Index and Data Insert [Link]
• Disable Index & Update Statistics [Link]

(*All the above mentioned links are from Pinal Dave, a.k.a. “SQLAuthority” [B|T]’s blog)

Until we meet next time,

Be courteous. Drive responsibly.