Hi Friends,

To understand SQL Server Table Spool Operator, let us consider following query as an example.

TableSpoolGrphPlan

TableSpoolTextPlan

For each SalesOrderID from the SalesOrderDetails table the sub-query returns the average value of all OrderQuantity and after that average is compared with principal query to filter each SalesOrderID’s with values higher than their average.

Lazy Spool reads data only when individual rows are required and creates a temporary table then build this table in a “lazy” manner; that is, it reads and stores the rows in a temporary table only when the parent operator actually asks for a row.

As can be seen, Spool operator displayed three times in execution plan but that doesn’t mean that three temporary tables are being created in memory that to say, all the spools are using same temporary table and this can be verified if we look at the operators hints displayed in execution plan.

ToolTip1ToolTip2ToolTip3

As we see, the first spool hint has NodeID 2 and other two has referenced Primary Node ID 2. Next two days we are going to discuss on the execution plan for the query used in our example.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook