Interviewer asked me the difference between clustered and non-clustered index and than moved further into it and asked one more question - When a table has a clustered index and a non-clustered index, do the non-clustered index still points to the data row in the table? I said yes, and immediately I thought it is wrong. Does it points towards the data row that is ordered by clustered index or the original data rows? Can someone throw some light on this please.
Asked
Active
Viewed 1,143 times
0
-
1https://msdn.microsoft.com/en-us/library/ms190457.aspx – Richard Brightwell Dec 07 '15 at 05:11
-
1@MitchWheat I couldn't find it straight away, so thought of sharing it on SO, because it might be helpful for someone else as well..be positive and spread love..:-) – Learner Dec 07 '15 at 05:12
2 Answers
1
A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.
When a query is issued against a column on which the non-clustered index is created, the database will first go to the non-clustered index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.
Santhoopa Jayawardhana
- 1,048
- 7
- 18
0
The actual table is stored in the order of Clustered index column.The non clustered index key column are stored in different table apart from the actual table.
There is a nice article on Clustered and non clustered index below:
it will give you a nice idea on indexing:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/14/sql-server-part-4-explaining-the-non-clustered-index-structure-/
How NonClustered Index works in SQL Server
