Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

Question

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.

Answer 1

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there'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.

Answer 2

From Why Doesn’t SQL Server Use the RID for Lookups from a Nonclustered Index into a Clustered Table? by Paul Randal:

Actually, that is almost how versions prior to SQL Server 7.0 worked. In prior versions (before 7.0) SQL Server used a volatile RID – which would follow the row on every update. More specifically, when a row was updated in these earlier versions and the row needed to physically move within the clustered index, then all of the nonclustered indexes needed to be updated as well.

Given that you can have 249 nonclustered indexes on each table, the work to update these nonclustered indexes could become terribly expensive (in terms of time, disk space, logging, etc…). As a result, the architecture was changed in SQL Server 7.0 to use the clustering key (instead of this volatile RID) – if the table is clustered.

This is an optimization so that even when a row must be relocated the nonclustered indexes don’t need to be updated (as long as the clustering key value doesn’t change).

There is a similar optimization for heaps (forwarding records). See the article for full details.