deroby (7/6/2012)
However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only.Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??
Most likely no. SQL is optimised for tables having clustered indexes. While a RID lookup will read less pages than a key lookup, the lookup is still a horridly slow operation that you want to avoid most of the time.
I agree that if queries attack the table using the clustered index keys directly the lack of a bookmark lookup makes them 'winners'. But in our case we have at least 2 other totally different approaches in accessing the same table and then those need to go via a rather expensive bookmark lookup now... making it a heap would make every approach more or less equally good/bad...
Why does two approaches automatically mean lookups?
Even if it did, making the table a heap would probably make both equally bad, which is probably not the optimisation strategy I would favour.
The only reason NOT to work like this would be that whenever a table-change affects a record to move from one page to another this would affect all the leaf-nodes of every index ...
When rows are moved in a heap (typically when they are updated and grow in size) they leave forwarding pointers behind, the nonclustered indexes aren't updated because that would be expensive.
Forwarding pointers make reads of the heap more expensive than it would be othewise.
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