SQL Server, the Row Count Spool (explained in a simple way)

Hi guys,

Welcome back friends!

Last  posts were dedicated to the spool operators, we have seen table spool, eager spool, lazy spool and window spool.

Today is the moment of the Row count Spool.

Do you want to know everything about this type of Spool operator? then you just have to read ..

 

The Row Count Spool

What is the Row Count Spool? 

It is simply one of the four spool operators supported by SQL server.

It counts the number of rows that receive in input and return these rows when needed.

This type of spool therefore behaves with a Table Spool but it is optimized for cases where the only relevant information is the number of rows and the content of the rows is not.

How does SQL Server implement such behavior?

Through a simple but smart idea!

The data, that are read only to obtain the cardinality, are not saved.

Note that since the operator does not store its own input, no working table is created in the tempdb.

This operator simply returns the same number of rows as its input but empty!

When do we come across this type of coil?

For example, when we use an EXISTS predicate and the subquery is unrelated to the main query, or when we force the optimizer to use a nested loop join.

Let's see an example of this last case ..

The Example

For the example we will use our standard tables already used many many times, ordtes and ordrig.
We create and populate our table through these commands:

Create table OrdTes (
  id int identity(1,1) not null primary key,
  dateDoc datetime,
  customer varchar(40),
)

Create table OrdRig (
  id int identity(1,1) not null primary key,
  IdOrdTes int,
  Product varchar(40),
  FOREIGN KEY (IdOrdTes) REFERENCES OrdTes(Id)
)

Insert into OrdTes (datedoc, customer) values (4400,'LUKE')
Insert into OrdRig (IdOrdTes, Product) values (1,'A'),(1,'B'),(1,'C')

Let's try this statement.

If you notice the part highlighted in yellow you can see that we have forced a nested join

Select r.Product from OrdRig r
  Inner loop join OrdTes t on r.idOrdTes = t.id
where 
  t.id = 1

Let's look at the execution plan now.

Here is our row count spool!

Now we know what it does!

We also know that, in this example, actually we are not interested in any data of the ordtes table (it is not extracted in the select). Of the Ordtes table we are only interested in cardinality as information that is used to construct the execution plan.

Note: Removing the forcing of the join type the row count operator is removed (actually it appears but it is superfluous)

I will not go into further details on how it works and so that's it for today!
I hope I was clear and that you found this post interesting.

If so follow me on linkedin by pressing the follow button, this is a way to help the growth of this blog !!! 

 

 

 

 

 

 

Previous post: SQL Server, the window spool operator (and the window functions)