I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.
The Scenario
Imagine that you have an index on a table. In my case, I created this index:
CREATE INDEX LoggerNCI ON dbo.Logger (LogID)
I can then disable this index with the following code:
ALTER INDEX LoggerNCI ON dbo.Logger DISABLE
I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.
It’s resume. This code turns the index back on and updates it.
ALTER INDEX LoggerCI ON dbo.Logger REBUILD
I can also use either of these items:
CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING DBCC DBREINDEX(Logger, LoggerNCI)
Interesting short moment the other day as I realized there are a few options here.
SQL New Blogger
While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.
A nice short way to showcase some learning.