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.