Disable, don’t delete those indexes

A quick post this week, since it’s that time of the year when people do gift exchanges and put up colourful lights.

Here goes: If we need to remove an unused index, we should disable it, not delete it.

In SQL Server Management Studio, we can do this in the Object Explorer by right-clicking on the index and selecting Disable.

As with every Microsoft product, there’s more than one way to do something, so we can also disable the index with T-SQL code.


USE <dbname>;
GO
ALTER INDEX IX_Name_Of_Index ON <tablename> DISABLE;
GO

What this does is keep the index metadata (the definition) but removes the index itself from the database.

If for any reason we need to recreate the index, we don’t need to go into source control to find the definition again. We just rebuild the disabled index, and it will come right back.

Another example use case is for indexes that are only used once a month during reporting periods. We may have a script that recreates them from scratch, but by disabling them instead, we only need to rebuild to get them back to their former glory.

Share your other index shortcuts with me on Twitter, at @bornsql. And happy holidays!

Photo by Michał Grosicki on Unsplash.