A window function computes a value for each row from a set of related rows (the window) without collapsing them the way GROUP BY does. The OVER clause defines the window with three optional parts: PARTITION BY, ORDER BY and a ROWS or RANGE frame. Figure 4.1 applies all three parts to a few sample rows.
Part of OVER |
Purpose | If omitted |
|---|---|---|
PARTITION BY |
Divides rows into groups; the calculation restarts per partition | The whole result set is one partition |
ORDER BY |
Logical order within each partition | Aggregate functions use every row in the partition |
ROWS/RANGE |
Frame of rows relative to the current row (requires ORDER BY) |
With ORDER BY present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for functions that accept a frame |
Window functions fall into four families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), aggregate (SUM, AVG, COUNT, MIN, MAX and others with OVER), analytic (LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC) and NEXT VALUE FOR with a sequence.
Where window functions can appear
The logical processing order of a SELECT is FROM, ON, JOIN, WHERE, GROUP BY, WITH CUBE/ROLLUP, HAVING, SELECT, DISTINCT, ORDER BY, TOP. Window functions are evaluated with the SELECT list, after WHERE, GROUP BY and HAVING, so they can be used in the select list and ORDER BY but not in WHERE. To filter on a window result, compute it in a CTE or derived table and filter in the outer query. Fabric Data Warehouse and the SQL analytics endpoint add a QUALIFY clause for this, but QUALIFY isn’t available in SQL Server, Azure SQL or SQL database in Fabric.
Other rules from the OVER clause reference:
PARTITION BYand the windowORDER BYcan refer only to columns available from theFROMclause, not to select-list aliases, and the windowORDER BYcan’t use a column position number.OVERcan’t be combined withDISTINCTaggregates such asCOUNT(DISTINCT col) OVER (...).- Several window functions with different
OVERclauses can appear in one query.
Performance
A rowstore index whose key is the PARTITION BY columns followed by the ORDER BY columns (in matching order), with the aggregated columns as INCLUDE columns, lets the engine avoid a sort. Window Aggregate operators can run in batch mode on tables with a columnstore index, or on rowstore tables at compatibility level 150 or higher; batch mode can’t be forced, so check the operator’s Actual Execution Mode. Accurate statistics on the partitioning and ordering columns, and memory grant feedback (compatibility level 140+), help avoid sort spills.
CREATE INDEX IX_OrderHeader_Customer_OrderDate
ON Sales.OrderHeader (CustomerID, OrderDate)
INCLUDE (TotalDue);
-- supports:
SELECT CustomerID, OrderDate,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM Sales.OrderHeader;Common trap: Writing
WHERE ROW_NUMBER() OVER (...) = 1- window functions are evaluated afterWHERE, so this fails; compute the number in a CTE and filter outside it (QUALIFYexists only in Fabric Data Warehouse and the SQL analytics endpoint).