This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_Students ( StudID INT IDENTITY(1,1) ,Name VARCHAR(10) ,CONSTRAINT pk_tbl_Students_StudID PRIMARY KEY(StudID) ) GO |
Create a NonClustered index on Name column:
|
1 2 |
CREATE NONCLUSTERED INDEX idx_tbl_Students_Name ON tbl_Students(Name) GO |
Insert 10000 dummy records:
|
1 2 |
INSERT INTO tbl_Students(Name) VALUES ('Anvesh') GO 10000 |
Now, Disable the Clustered Index:
It also disables the all Nonclustered Indexes of a table. You can check below warning message***
|
1 2 |
ALTER INDEX pk_tbl_Students_StudID ON dbo.tbl_Students DISABLE GO |
|
1 |
Warning: Index 'idx_tbl_Students_Name' on table 'tbl_Students' was disabled as a result of disabling the clustered index on the table. |
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.
|
1 2 |
SELECT *FROM tbl_Students GO |
|
1 2 |
Msg 8655, Level 16, State 1, Line 1 The query processor is unable to produce a plan because the index 'pk_tbl_Students_StudID' on table or view 'tbl_Students' is disabled. |
The solution is, Rebuild your Clustered Index and you will get your table back:
|
1 2 |
ALTER INDEX pk_tbl_Students_StudID ON dbo.tbl_Students REBUILD GO |
