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 iterator performs a single simple function, such as applying a filtering condition, or performing an aggregation. It can represent a logical operation, a physical operation, or (most often) both.
For example, ‘Aggregate’ is a logical operation, and Stream Aggregate and Hash Aggregate are physical operations. Similarly, ‘Inner Join’ is a logical operation; Nested Loops a physical one. Both graphical plans and XML showplan output show these properties: Read More...