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.
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).
- 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,