Tell me more ×
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.
up vote 5 down vote favorite
1

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
7  
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 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 at 12:17
5  
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 at 12:34
1  
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 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 at 17:05

2 Answers

up vote 3 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
up vote 1 down vote

First point: a table is either a heap (has no clustered index) or a clustered table (has a clustered index). One table can have at most on clustered index, since there is only one way to sort a table at one point in time.

When creating a clustered index, SQL Server sorts the data pages and creates a b-tree indexing the pages (not the rows - non-leaf pages index the pages at the next level down). The leaf pages of the b-tree are the data pages. Thus, disabling the index disables all access to the table.

When creating a nonclustered index, SQL Server duplcates the indexed data in a series of pages then creates a b-tree indexing those. In this case the leaf pages of the b-tree are those pages containing the duplicated data (with one index entry in the leaf pages for every row in the data pages). Thus, disabling the index does not affect access to the table.

Second point (a personal niggle): indexes cannot be enabled - only rebuilt. Disabling an index deletes the non-leaf pages.

Books Online links: Clustered and Nonclustered Indexes Described, Disabling Indexes, Reorganizing and Rebuilding Indexes.

share|improve this answer
3  
I'm not seeing how this answers the question at hand. – Mark Storey-Smith May 6 at 0:03
thanks for making a point – sarepta May 6 at 20:32

Your Answer

 
discard

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.