MVP Hugo Kornelius once reported that he encountered a situation in which it was possible to perform a table scan on more pages than actually existed in the heap table. Hugo deduced that this was due to a phenomenon called “forwarding pointers”.
Why in the world would this ever happen? Real Paul Randall’s excellent blog at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspxfor a hint:
“Another drawback \[of heaps\] is that when scanning through the heap, forwarding records have to followed immediately (as opposed to ignoring them and just reading the forwarded records when they're encountered). This is to vastly reduce the possiblity of read anomalies (such as non-repeatable reads or missed rows if a row moves before the scan point during a scan).”
Evidently, SQL Server chooses some times when it’s appropriate to ignore forwarding pointers for a speed advantage when scanning the entire table.
Interesting details, though minute, to be sure. Enjoy,
-Kev
