GilaMonster (11/11/2009)
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.
i don't think i ever found a straight answer to this question
if i have a table with a clustered index and say 5 NC indexes
i run alter index <clustered_index> on <some_table> rebuild
will this also rebuild all the non-clustered indexes as well? i've noticed that the clustered index rebuilds take a lot longer to complete