Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When an index is disabled, the definition remains in the system catalog but is no longer used.
SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to satisfy queries.
If a Clustered Index is disabled, the entire table becomes inaccessible.

The question is:
why isn't it possible to access the data directly from the table discarding the B-tree?
(most likely by scanning the table row by row)
wouldn't that be more appropriate than inaccessible data at all?

share|improve this question
The leaf level of the B+ tree is the table. What are you hoping to achieve by disabling the CI? Just don't do this if you don't want the data to be inaccessible. –  Martin Smith May 4 '13 at 12:13
its pure theoretical, i would never do that, plus i did know that the leaf of a clustered index contains the row, but i didn't know that it's the only copy, i thought that issuing any DML command will update the index, but i never thought that it is the actual table data. –  sarepta May 4 '13 at 12:17
I'm not really sure why SQL Server even allows you to do this. CREATE TABLE T(X INT CONSTRAINT PK PRIMARY KEY CLUSTERED, Y INT CONSTRAINT UQ UNIQUE NONCLUSTERED);ALTER INDEX PK ON T DISABLE also disables the NCI so even SELECT queries that would be covered by that are disabled. I can't think of any use case for this. –  Martin Smith May 4 '13 at 12:34
The only use I can think of off my head is exactly what is being discussed. Disabling a table. If you have a table that you don't want anyone touching, even dbo or sysadmin, then you can disable the clustered index. The table exists along with the data, but is completely inaccessible until you re-enable the clustered index. –  Kenneth Fisher May 4 '13 at 16:44
guys its not a scenario, nor a to-do thing, i just want to know why thing goes that way, consider it sq-internals question –  sarepta May 4 '13 at 17:05
add comment

1 Answer

up vote 5 down vote accepted

why isn't it possible to access the data directly from the table discarding the B-tree? (most likely by scanning the table row by row) wouldn't that be more appropriate than inaccessible data at all?

To answer your question, Indexing basics comes more handy -- An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. For more details refer here.

Also, as many people have described, Clustered Indexes == Original tables which are physically ordered with one or more keys or columns. So, when a clustered Index is disabled, its data rows cannot be accessed. You wont be able to Insert any data (for Non Clustered Index the Insert will succeed -- but that is not entirely related to this post -- as here the discussion is of Clustered Index) as well or neither Reorganize operation will work.

Below will explain you in detail :

we will use Adventureworks database to see the effect of disabling the CLUSTERED Index.

enter image description here

Now check the row count in the table:

enter image description here

Now disable the Clustered Index

enter image description here

Now select the row count from the table. This time it will error out with below message:

enter image description here

Even the reorganize operation does not work !!

enter image description here

Now rebuild the Clustered Index and it should work fine.

enter image description here

Select the table to see if we can access the data

enter image description here

So the bottom line is that, if we disable the Clustered Index, then Data in the table still exists, but will not be accessible for anything other than Drop or REBUILD operations. All related Non-clustered Indexes and views will be unavailable as well as Foreign Keys referencing the table will be disabled and there by leading the FAILURE for all the queries that are referencing the table.

Note: There is no option to ENABLE the Index. You have to REBUILD it.

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.