Elliott Whitlow (12/1/2011)
Gila is that correct. If the record moves at all doesn't the pointer change as well?
If a table has a clustered index, the 'pointer' is the key value for the clustered index. That certainly doesn't change when an index is rebuilt (if I have a cluster on an identity column, a rebuild doesn't cause the row with an ID of 50 to suddenly become 12)
It was my understanding that this was true and the underlying cause that when a DBCC DBREINDEX was performed that all non-clustered indexes HAD to be redone because the pointers changed? Is this inaccurate?
Nonclustered indexes aren't redone when the clustered index is rebuild. Rebuilding a clustered index just rebuilds the cluster, the nonclustered indexes aren't touched (on SQL 2000 if the cluster wasn't unique then nonclustered indexes would be rebuild when the clustered index was, that was fixed in SQL 2005)
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