SQL Server, the window spool operator (and the window functions)

Hi Guys,

In the last post we talked about the Lazy Spool operator, today to continue the discussion we will talk about another type of spool: the window spool.

We will tell how it works and when the optimizer uses this type of spool.
Enjoy the reading!

 

The window spool

Let's say the window spool is both a logical and a physical operator.

The optimizer uses this type of spool when dealing with a query that contains a Windows function.

I am sure you already know what a windows function is, ...but in case there is someone just entered in the SQL world I gladly remember it ..

A window function is simply a function that can operate on a set of rows and return a single aggregated value for each row.

For example i can compute an aggregate value without group data.

The highlighted part of the string is the window function:

SELECT [NAME], [SALARY], MAX([SALARY]) OVER (PARTITION BY [NAME] ORDER BY [NAME]) AS MAX_SALARY FROM [EMPLOYEE] order by [NAME], [SALARY]
 

This function returns the MAX salary value for each name:

In the resultset i have both the aggregate value and the detail of all the data.

While, if I had used the group by I would have lost the details of the various salaries.

But let's take a step forward ..

The window spool operator takes its name from the window function precisely because this operator is used to implement this function!

To see it well let's look at the following example.

 

How does it work?

Let's look to the execution plan of the T-SQL command we just talked about.

Yes, we have the window spool operator!

but what is it for?

let's make a premise...

In a window function the over clause defines a window within which it can compute an aggregate value for each row.

In this example infact for each row where the name is equal to LUKE we have a window frame consisting of all (the 3) rows where the name is equal to LUKE.

The window spool operator store on a worktable the default window frame.

In this way for each row it can perform the required calculations

 

Well! although we certainly haven't said everything about the window spool ... 

that's all for today!

I look forward to seeing you at the next post!

Luca

 

 

 

 

Previous post: SQL Server, execution plan and the lazy spool (clearly explained)