SQL Server Sad Clustered Index

SQL Server Interview: A Golden question, Can we disable the Clustered Index?

 Many people asked me, Are you doing an only blogging or consulting? Every time my clear answer was, I am doing full-time Job as DB Lead in one of the leading IT Companies (working for 9 hours to 10 hours) and

As you guys know, that I have shared loads of SQL Server Interview questions and answers.
Let me tell you a story behind this,

Whenever I free in a weekend; I always try to attend walk-in interviews which have been arranged by most of the IT Companies so that I can find some real interview questions and can share with you guys.

Wait wait wait…. I know most of the people are thinking that I am running for a job… which is false.

I am not going to change my current company, but I am attending most of the interviews because I want to measure myself so that can share something more and can also take superb interviews that I am also doing in my current company. Additionally spending minimum 3 hours to 4 hours every day for blogging…

I know, you guys are suffering my blah blah blah…. from the above two paragraphs, but for a golden question, you guys could suffer sometimes.

Last week, I attended a walk-in interview for DBA position at Tech Mahindra Hyderabad, India.
They asked few questions on Clustered Index, and I was empty entirely.

Questions were:

  • Can we disable the Clustered Index?
  • Have you ever tried to disable Clustered Index?
  • Once you disable the Clustered Index, Can we access the table?
  • Your table has Clustered Index + NonClustered Indexes. If you turn off the Clustered Index, Can you access your table using NonClustered Indexes?
  • Can we take the table in offline mode by disabling the Clustered Index?
  • Ok, If you disable the Clustered Index, how can you enable it again?

My simple answers were:

  • I don’t know it is possible or not
  • I never tried to disable the Primary key, and if it is possible
  • I don’t know how we can find the data path to access the table

Now, I don’t want to write more blah, blah, blah… Find the below full demonstration and test it yourself and at the end, you will get all answers to above questions.

All the best....

Now, one more question to you guys. If you have heap table, what ?, Can anyone share their thoughts via putting some comments?

Create a sample table:

Create a NonClustered index on Name column:
Insert 10000 dummy records:
Now, Disable the Clustered Index:
It also disables the all Nonclustered Indexes of a table. You can check below warning message***
Try to select a table:
You will get a below error, You cannot select the table data because of query optimizer unable to find the access path of data.
The solution is, Rebuild your Clustered Index and you will get your table back:
Anvesh Patel

Leave a Reply

4 Comments on "SQL Server Interview: A Golden question, Can we disable the Clustered Index?"

Notify of
avatar
Sort by:   newest | oldest | most voted
Amit
Guest

Thanks for sharing the update, in general, if we have heap table in database so it doesn’t contain any dependency with indexes. It does work faster in bulk operation.

Jay
Guest

Perfect !!! Loved to learn this and another true facts there is no ENABLE OPTION you can only REBUILD or DROP and CREATE the index.

This is not the case with non clustered indexes.

pabhat
Guest

yes good, love to visit this site..at the same time we can disable the non clustered index and we can select the table.

wpDiscuz