.NET and SQL Server Interview Question - What are difference between Cluster index and Non-Cluster index?

 Posted by ArticlesMaint on 4/5/2011 | Category: Sql Server Interview questions | Views: 4083

Both of these indexes uses "B-tree" structure but in Cluster index the "Leaf Node" actually points the physical data, but in Non-Cluster index it point’s to the "Row ID" and then the "Row ID" points to the "Leaf Node" of Cluster Index.

Below is the diagram of Cluster and Non-Clustered index.


Cluster Index Non-Cluster Index
A table can have only one Cluster Index as it point to the physical data. A table can have more than one Non-Cluster Index as it only points to the pointer of Cluster Index.
Physical data is stored as per Cluster Index. There is no relation of physical data.
The leaf node of Cluster Index consist of data pages. The leaf node of Non-Clusted Index contain Index row.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response