MSDN - Query Plan OperatorsShowing the plan in text:
SET SHOWPLAN_ALL ON;
GO
SELECT Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode BETWEEN 'Each' AND 'Inch';
GO
SET SHOWPLAN_ALL OFF;
Clustered Index
Seek operator uses the seeking ability of indexes to retrieve rows from a clustered index.
Clustered Index
Scan operator scans the clustered index specified in the Argument column of the query execution plan.
RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the bookmark label used to look up the row in the table and the name of the table in which the row is looked up. RID Lookup is always accompanied by a NESTED LOOP JOIN.
The
Nested Loops operator performs the inner join, left outer join, left semi join, and left anti semi join logical operations. Nested loops joins perform a search on the inner table for each row of the outer table, typically using an index. Microsoft SQL Server decides, based on anticipated costs, whether to sort the outer input in order to improve locality of the searches on the index over the inner input.
The
Spool operator saves an intermediate query result to the tempdb database.
The
Eager Spool operator takes the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.
The
Lazy Spool operator builds its spool file in a "lazy" manner, that is, each time the spool's parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.