Table Spool (Eager Spool) operator in SQL Server execution plan

 The Table Spool (Eager spool) operator is used in execution plan to temporarily store query result in tempdb (Temporary storage). The Eager spool variant specifically stores rows as soon as they are produce by lower operator, insuring that the data is available use in the execution plan. It is typically used when the same data needs to be reused multiple times in the query.

It generate on the below scenarios

Ø  When we are dealing with the nested loop joining that require the inner side of join to be reused multiple time.

Ø  Cache intermediate result for reuse in sub query steps for the query.

Ø  Complex sub queries

Ø  CTE

Ø  Correlated sub query

Icon of Table Spool (Eager Spool) Operator

 See the example

Creating a table and index

CREATE TABLE demo_tbl_eager_Spool    ( Id INT IDENTITY(1, 1) , Nm VARCHAR(200) )       CREATE CLUSTERED INDEX IX_demo_tbl_eager_Spool_nm ON demo_tbl_eager_Spool(nm ASC)

Now inserting some records into this table

INSERT INTO demo_tbl_eager_Spool(nm) values ('Bagesh') GO 100

Now updating the nm column.

UPDATE demo_tbl_eager_Spool set nm = cast (NEWID() as varchar(200))

Run this script and see the execution plan