Microsoft SQL Server 2012 Internals

Windowing functions

SQL Server 2012 introduces a new kind of spool associated with windowing functions. Windowing functions can compute moving averages and running totals, and the Window Spool operator is specially crafted for this kind of scenario. Spools can work in different ways. The most traditional query processing spool algorithm operates by consuming all rows from its input operators in the query tree before returning rows to the client. Other spools can cache rows as they flow through the operator (lazy spooling) for more efficient reuse later in the query. For the windowing functions, a new operator was created that spools rows based on the active windows of an ordered stream computation.

The Window Spool works as follows:

• It consumes ...