Query Optimization and Execution - Pro SQL Server Internals - page 466

Let's look at an example and use the table we created in Listing 25-11. We will run the query that returns
information about all of the orders, together with the total amount of sales on a per-customer basis, as shown in
Listing 25-13.
Listing 25-13. Table Spool example
select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales]
from dbo.Orders
The execution plan for the query is shown in Figure 25-11 . As you can see, SQL Server scans the table, sorts the
data based on the CustomerID order, and uses a Table Spool operator to cache the results. This allows SQL Server to
access the cached data and avoid an expensive sorting operation later.
Figure 25-11. Execution plan of the query
Even though a Table Spool operator is shown in the execution plan several times, it is essentially the same
spool/cache. SQL Server builds it the first time and uses its data later.
As a side note, this is a great example of the declarative nature of SQL. The query in Listing 25-14 is the logical
equivalent of the query in Listing 25-13, and both of them have the same execution plan.
Listing 25-14. Alternative version of the query
select o.OrderId, o.CustomerID, o.Total, ot.Total
,os.TotalSales as [Total Customer Sales]
from
dbo.Orders o join
(
select customerid, sum(total) as TotalSales
from dbo.Orders
group by CustomerID
) ot on
o.CustomerID = ot.CustomerID
SQL Server uses spools for Halloween Protection when modifying the data. Halloween Protection helps you
avoid situations where data modifications affect what data need to be updated. The classic example of such a situation
is shown in Listing 25-15. Without Halloween Protection, the insert statement would fall into an infinite loop, reading
the rows it has been inserting.