From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index.
When there is not a clustered index (Heap) as I understand it Nonclustered indexes reference directly into the pages of the table.
Assuming correct understanding, why do the non-clustered indexes reference the clustered index instead of the underlying data pages of the table directly? It seems like this would be less overhead referencing the table data pages directly.
Best Answer
The clustered index IS the table. In the classic phone book analogy where everything is ordered by
Lastname, Firstnamethere's not some other book that the phone book refers to. The Phone book IS the book, and it's in that order.When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick.
In the absence of a CI, the non-clustered indexes need to refer to something so they refer to the RID (row identifier) which is a surrogate key the engine assigns.