Sql-server – SQL Server – How are data pages stored when using a clustered index

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full.

Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, and a huge performance killer. You might have 5, 10, 20 nonclustered indices on your table, and SQL Server would have to scan all those indices (basically scanning the whole index, all rows in the index, and that 10, 20 times) and update all RIDs.... that's just not practical - very quickly so.

If you store the value of the clustering index as a "row pointer" instead, that value should typically never change - and most definitely it doesn't need to be updated every time a page is split. Yes, it does involve a second index seek operation - the key lookup - but for simple scenarios, retrieving a single row or a few rows, that's still much more efficient than anything else.

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.