Esalter (11/11/2009)
I'm surprised that non clustered indexes don't also use the RID to provide a more efficient use of space.
A RID is 8 bytes. An integer column defined as identity (a very good and popular choice for clustered index) is 4 bytes.
This is of course countered by the need to update all non clustered indexes when a row is moved to a different page as the result of a split operation
And the need to rebuild every single nonclustered index when the cluster is rebuilt. Painful in terms of logging.
The need to update all the nonclustered indexes is, I believe, one of the reasons the RID is not used as the row 'pointer' when there's a clustered index. Even when the underlying table is a heap, when a row is moved the RID in the NC indexes is not updated, rather a forwarding pointer is left behind which says where the row has moved to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability