Window Spool operator in SQL Server execution plan

The Window Spool operator in SQL Server is a logical operator used in execution plans to optimize queries that involve window functions. It temporarily stores the rows needed for calculations (like running totals or rankings) and reuses them across multiple rows without rescanning the original data. The Window Spool operator helps manage data when evaluating windowed or partitioned functions like ROW_NUMBER, RANK, SUM, AVG, or other functions with an OVER clause.

Icon of Window Spool operator

 

See the example

Creating a table and inserting some data in this table for the demo

CREATE TABLE Sales (     SaleID INT PRIMARY KEY,     ProductID INT,     Quantity INT,     SaleDate DATE ); INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate) VALUES (1, 101, 5, '2023-01-01'),        (2, 102, 2, '2023-01-02'),        (3, 101, 3, '2023-01-03'),        (4, 103, 7, '2023-01-04'),        (5, 101, 4, '2023-01-05');

Running the below table.

SELECT     SaleID,     ProductID,     Quantity,     SaleDate,     SUM(Quantity) OVER (PARTITION BY ProductID ORDER BY SaleDate) AS CumulativeQuantity FROM Sales;

See the result

See the execution table