Disabling Clustered Indexes in SQL Server – a really good idea?

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Last week I taught an internal SQL Server Performance Tuning course to a customer in Switzerland. As usual we had a lot of fun, and at some point an attendee asked me one of the best questions of my life: “Can you disable a Clustered Index?

At first I was really scared about that question, because I had never ever thought about disabling a Clustered Index, because the Clustered Index represents the table data. My short answer to this very interesting question was “I think so, but…

So let’s talk now about the “but”. I really had no idea how SQL Server reacts when you disable the Clustered Index, so let’s try it.

-- Let's disable a Clustered Index
ALTER INDEX PK_Person_BusinessEntityID ON Person.Person DISABLE

The statement simply works! Wow! But you get a lot of different warnings that Foreign-Key Constraints are also disabled. Ok, so we have disabled the Clustered Index of a table, but we have effectively disabled the table itself. Let’s run a simple SELECT statement against the disabled Clustered Index.

-- Let's run a SELECT statement against the disabled Clustered Index
SELECT * FROM Person.Person

Ouch!!! The Query Optimizer can no longer produce an Execution Plan, and we have broken our SQL statement!

Msg 8655, Level 16, State 1, Line 23
The query processor is unable to produce a plan because the index ‘PK_Person_BusinessEntityID’ on table or view ‘Person’ is disabled.

When you think about it, that makes sense: we have disabled the Clustered Index, which is the table itself! But maybe this shouldn’t really matter, because we probably also have some Non-Clustered Indexes which can be used as additional Data Access paths.

Let’s think about that for a second. Would it be possible to access the table data through a Non-Clustered Index? In theory yes, as long as the chosen Non-Clustered Index is a Covering Non-Clustered Index. If you also have to request some additional columns through a Bookmark Lookup from the Clustered Index, you again have a huge problem, because the Clustered Index is currently disabled. So let’s check the status of the Non-Clustered Indexes through a quick look into sys.indexes:

ALL our indexes are now DISABLED!

Wow! ALL our Non-Clustered Indexes are also DISABLED! There now in no way at all to access our table data. Again, this makes perfect sense, because otherwise we would introduce a huge amount of problems with necessary Bookmark Lookups. We have now effectively taken our table offline by disabling the Clustered Index!

How do we get our table back online? Simple: we have to rebuild our Clustered Index:

-- Let's rebuild the Clustered Index
ALTER INDEX PK_Person_BusinessEntityID ON Person.Person REBUILD

Quite simple, isn’t it? But when we look again at sys.indexes, we will see that our Non-Clustered Indexes are *STILL DISABLED*! Therefore it is very important that we afterwards *ALSO* rebuild all our Non-Clustered Indexes to get them back online.

The moral of the story? Never ever disable the Clustered Index of a table 😉

Like or share to get the source code.

Thanks for your time,



  • Wow, very nicely shown, much appreciated 😉

  • Natalya

    Klaus, thank you for yet another great article. Can you think of any scenario when someone would need to disable a clustered index? I am just wondering why this option is even available?

    • Klaus Aschenbrenner

      Hello Natalya,

      You can take your table with this option offline.
      That’s the use case for it.
      If it makes sense or not, is another question… 😉



  • Demo code’s link is broken.

    • Klaus Aschenbrenner

      Thanks for the info – the link is working now.


  • Bogdan

    Nice. FKs are also activated ? If yes they are validated or not (is_trusted = 0) ?

    • Klaus Aschenbrenner


      The FKs are also disabled and must be enabled manually again…



  • Susheel

    Gr8 thing to teach thanks i never thought for thid

  • Disabling clustering index may be a good way during partial SQL system maintenance. Maybe you have to make sure no data can go through. It’s an good option to know about as DBA.

    Thanks for sharing!

It`s your turn

Your email address will not be published. Required fields are marked *


Copyright © 2018 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Data Protection · Go to Top