When creating a non-clustered index, the leaf level will contain a reference to rows in the original table:
- if the table has clustered index, the leaf level will contain the clustered index key;
- if the table has no clustered index (a heap), the leaf level will contain a pointer to the physical address of the row in the table;
My question is why the non-clustered index wasn't designed to contain the physical address in both cases (in addition to clustered index key). It could save the reads of the B-Tree for key lookups when clustered index is defined.