Different types of showplan in SQL Server and Iterators

Check out Craig Freedman's WebLog on the different ways of viewing the query plan.


http://blogs.msdn.com/craigfr/archive/
2006/06/13/629615.aspx

Another interesting article on Craig's website is on the Building Blocks of Query Execution. In fact, I think the notion of iterators first came about in Goetz Graffe classic paper, titled Query evaluation techniques for large databases , and it have been widely studied in academic cirlces, as well as implemented in commercial database management systems. In fact, the idea is very elegant. Each physical operator (scan, hash-merge join, nested-loop) in a DBMS defines a basic set of operations (e.g. open, close, getNext() ).

In a query execution plan, these operators gets connected together in some ways (e.g. a tree-like manner). Then, the input to an operator is obtained from the output of another operator. Imagine a tree structure, at the top of a tree, you tell the operator to do a getNext() which then ask its children operators to do a getNext(), and so on and forth till you hit the leaf node (usually the scan operators), which will then fetch the data from the storage manager. In this way, data can flow in a pipelined manner.

The caveat to all this is that some operators are blocking (i.e. they cannot produce an output until they finish the task they are supposed to do). A classic example would be the sort operator. The challenge then is how to deliver partial results progressively...