This Q&A was previously posted on the Database Administrators Stack Exchange website.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the DBA Stack Exchange post category.
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.
asked 2022-09-25 by Fajela Tajkiya
Answer
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.
answered 2022-09-25 by Andy Mallon