A few days ago, Sandra Mueller (twitter | blog) asked a question using twitter’s #sqlhelp hash tag: “Might SQL Server retrieve (out-of-row) LOB data from a table, even if the column isn’t referenced in the query?” Leaving aside trivial cases (like selecting a computed column that does reference the LOB data), one might be tempted to say ...

If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

Iterators SQL Server uses an extensible architecture for query optimisation and execution, using ‘iterators’ as basic building-blocks.  Iterators are probably most familiar in their graphical showplan representation, where each icon represents a single iterator.  They also show up in XML query plan output as RelOp nodes. Each ...