Row Count Spool (Lazy Spool) operator in SQL Server execution plan

The row count spool operator is a variant of the spool operator that is used to count rows from child operator fully reading the underlying data multiple times. This operator is used in conjunction with other operator to optimized query performance in certain condition, especially when the number of rows need to be referenced multiple times.

It is similar to table lazy spool. It appear on below cases.

Ø  Correlated sub queries

Ø  Joins with condition

Ø  Filtering operation

Ø  Exists

 Icon of Row Count Spool in execution plan

Let’s see the example

Here we are creating two table and inserting some records

CREATE TABLE dept   (      dept_id   INT NOT NULL PRIMARY KEY,      dept_name VARCHAR(100),   ) CREATE TABLE emp   (      emp_id   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,      emp_name VARCHAR(200),      dept_id  INT,      FOREIGN KEY (dept_id) REFERENCES dept(dept_id)   ) Insert into dept (dept_id,Dept_name) values (1,'Admin'),(2,'IT'),(3,'Account'),(4,'HR') Insert into Emp(Emp_name,dept_id) values ('Bagesh',1),('Rajesh',2),('Ramesh',3),('Gamesh',4), ('Suresh',4),('Mahesh',4)

Run this script. Table created and rows inserted successfully.

Now running the below query and see the execution plan.

Select e.* from Emp e  where exists ( select d.dept_id from dept d where e.dept_id=d.dept_id and  d.dept_id = 4)

  

See the execution plan