What's the use case of disabling an index?

Question

I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can't access the index or the data if clustered index. When will we disable an index? I just don't understand the use case.

Answer 1

Disabiling an index (or all indexes on a table) can be very handy when you will (or might) need to replace the index.

For example, if you are removing a (seemingly) duplicate index, you can disable the duplicate and leave it disabled for some time while you confirm it can be dropped for good. If you find it is needed, you can simply reenable it without having to know the full definition. You still have to do a full rebuild, but don't need to remember/save the definition.

Similarly, for data loads where you may wish to load without maintaining indexes, you can more easily and dynamically disable all indexes, then reenable them without having to fully preserve the index definition like you would if you dropped and recreated them.

Answer 2

AMtwo covered non-clustered indexes well.

As for clustered index, that is a pretty weird thing to do. The only potential use-case I can think of is if you want to make sure that no-one can access the data. Of course, with sufficient permissions, you can just enable (rebuild) it again and access the data. So, I doubt you'd think of this as a security feature. In the end, it is likely just something exposed to us, which originally was used internally in SQL Server, with a very limited use-case (if any at all).

Note that if you disable a clustered index, then all non-clustered indexes and also foreign keys referring to that table are also disabled!

Disabling a clustered index on a view might be useful (Thanks Paul) if you don't want the storage overhead for that view for some reason, and later want to enable it. Note that queries on that view that uses the NOEXPAND hint will fail while the index is disabled.

Answer 3

How about the use case where the overhead of creating entries in an index during bulk updating is too costly, and therefore disabling the index, bulk update, reenable/reindex, would actually be quicker and cheaper.