Many RDBMSs support the concept of named windows – windows that we can reference from within an OVER clause when defining a window function.
For those of us using SQL Server, we had to wait until the release of SQL Server 2022 before we could create named windows for our window functions. But with the release of SQL Server 2022, we now have the option of using named windows in our window functions.
Named windows are made possible with the WINDOW clause of the SELECT statement. We use this clause to define the named window, and then we reference that window name in our SELECT list for each column that needs to use the named window.
Database Compatibility
The WINDOW clause requires database compatibility level 160 or higher. Here’s how to check your database’s compatibility level, and here’s how to change it (and here’s how to do both in SSMS).
Syntax
The syntax for the WINDOW clause goes like this:
WINDOW window_name AS (
[ reference_window_name ]
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>So we define it with WINDOW, followed by the window name, followed by AS (). We insert the window’s definition in between the parentheses.
Example
Here’s a basic example to demonstrate:
SELECT
District,
Name,
Population AS "City Population",
SUM(Population) OVER win AS "District Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
District Name City Population District Population -------------------- --------------- --------------- ------------------- Capital Region Canberra 322723 322723 New South Wales Sydney 3276207 3993949 New South Wales Newcastle 270324 3993949 New South Wales Central Coast 227657 3993949 New South Wales Wollongong 219761 3993949 Queensland Brisbane 1291117 1805236 Queensland Gold Coast 311932 1805236 Queensland Townsville 109914 1805236 Queensland Cairns 92273 1805236 South Australia Adelaide 978100 978100 Tasmania Hobart 126118 126118 Victoria Geelong 125382 2990711 Victoria Melbourne 2865329 2990711 West Australia Perth 1096829 1096829
Here, I created a named window called win. This named window has PARTITION BY District as its definition. We refer to that named window in our OVER clause, which causes the results to be partitioned by the District column.
Modifying the Definition
We can also define other criteria in the OVER clause when using named windows. This means we can define part of the window in the WINDOW clause, and part of it in the OVER clause.
This allows multiple columns to use the named window, but also have their own distinct criteria.
Here’s an example of what I mean:
SELECT
District,
Name,
Population,
SUM(Population) OVER win AS "Default",
SUM(Population) OVER(win ORDER BY Name ASC) AS "Ascending",
SUM(Population) OVER(win ORDER BY Name DESC) AS "Descending"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
District Name Population Default Ascending Descending -------------------- --------------- ----------- ----------- ----------- ----------- Capital Region Canberra 322723 322723 322723 322723 New South Wales Wollongong 219761 3993949 3993949 219761 New South Wales Sydney 3276207 3993949 3774188 3495968 New South Wales Newcastle 270324 3993949 497981 3766292 New South Wales Central Coast 227657 3993949 227657 3993949 Queensland Townsville 109914 1805236 1805236 109914 Queensland Gold Coast 311932 1805236 1695322 421846 Queensland Cairns 92273 1805236 1383390 514119 Queensland Brisbane 1291117 1805236 1291117 1805236 South Australia Adelaide 978100 978100 978100 978100 Tasmania Hobart 126118 126118 126118 126118 Victoria Melbourne 2865329 2990711 2990711 2865329 Victoria Geelong 125382 2990711 125382 2990711 West Australia Perth 1096829 1096829 1096829 1096829
In this case, three columns use the named window for its PARTITION BY clause. But two of the columns have extra criteria, in the form of ORDER BY clauses.
But it’s important to note that we can’t double up on clauses. We can only add clauses that aren’t in the named window. For example if the named window has a PARTITION BY clause, we can’t add another PARTITION BY clause to a column that refers to that named window. In other words, we can’t overwrite the definition in the WINDOW clause, we can only add to it.
Here’s an example to demonstrate what I mean:
SELECT
District,
Name,
Population,
SUM(Population) OVER(win PARTITION BY District) AS "Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
Msg 4123, Level 15, State 2, Server bd79c358ea10, Line 69 Window element in OVER clause can not also be specified in WINDOW clause.
In this case I tried to use two PARTITION BY clauses – one in the named window and one in the column that refers to that named window. This is not allowed and so I got an error.
Named Windows for More Concise Code
Named windows are a handy feature that can help us to make our code more concise. Named windows provide us with the opportunity to reuse code, so that we don’t need to replicate the same code multiple times.
Here’s an example to demonstrate:
SELECT
ProductPrice,
ROW_NUMBER() OVER win AS "Row Number",
RANK() OVER win AS "Rank",
DENSE_RANK() OVER win AS "Dense Rank"
FROM Products
WINDOW win AS (ORDER BY ProductPrice);Result:
ProductPrice Row Number Rank Dense Rank
------------ -------------------- -------------------- --------------------
9.99 1 1 1
10.00 2 2 2
11.99 3 3 3
12.45 4 4 4
14.75 5 5 5
25.99 6 6 6
25.99 7 6 6
33.49 8 8 7
55.99 9 9 8
245.00 10 10 9
Here, all three windows leverage the named window for its ORDER BY clause.
While it’s true that we still have to replicate the window name (in this case win), that still results in less code than what we would’ve had if we’d replicated its definition (i.e. ORDER BY ProductPrice). This effect would be even greater if the definition included a PARTITION BY clause and a ROW or RANGE clause.
Defining Multiple Named Windows
We can define multiple named windows with a single WINDOW clause. To do this, we simply separate them with a comma:
SELECT
District,
Name,
Population,
SUM(Population) OVER win1 AS "Default",
SUM(Population) OVER win2 AS "Ascending",
SUM(Population) OVER win3 AS "Descending"
FROM City
WHERE CountryCode = 'AUS'
WINDOW
win1 AS (PARTITION BY District),
win2 AS (PARTITION BY District ORDER BY Name ASC),
win3 AS (PARTITION BY District ORDER BY Name DESC);Result:
District Name Population Default Ascending Descending -------------------- --------------- ----------- ----------- ----------- ----------- Capital Region Canberra 322723 322723 322723 322723 New South Wales Wollongong 219761 3993949 3993949 219761 New South Wales Sydney 3276207 3993949 3774188 3495968 New South Wales Newcastle 270324 3993949 497981 3766292 New South Wales Central Coast 227657 3993949 227657 3993949 Queensland Townsville 109914 1805236 1805236 109914 Queensland Gold Coast 311932 1805236 1695322 421846 Queensland Cairns 92273 1805236 1383390 514119 Queensland Brisbane 1291117 1805236 1291117 1805236 South Australia Adelaide 978100 978100 978100 978100 Tasmania Hobart 126118 126118 126118 126118 Victoria Melbourne 2865329 2990711 2990711 2865329 Victoria Geelong 125382 2990711 125382 2990711 West Australia Perth 1096829 1096829 1096829 1096829
Window Chaining
Named windows can refer to other named windows in the same WINDOW clause.
Example:
SELECT
District,
Name,
Population,
SUM(Population) OVER win1 AS "Default",
SUM(Population) OVER win2 AS "Ascending",
SUM(Population) OVER win3 AS "Descending"
FROM City
WHERE CountryCode = 'AUS'
WINDOW
win1 AS (PARTITION BY District),
win2 AS (win1 ORDER BY Name ASC),
win3 AS (win1 ORDER BY Name DESC);Result:
District Name Population Default Ascending Descending -------------------- --------------- ----------- ----------- ----------- ----------- Capital Region Canberra 322723 322723 322723 322723 New South Wales Wollongong 219761 3993949 3993949 219761 New South Wales Sydney 3276207 3993949 3774188 3495968 New South Wales Newcastle 270324 3993949 497981 3766292 New South Wales Central Coast 227657 3993949 227657 3993949 Queensland Townsville 109914 1805236 1805236 109914 Queensland Gold Coast 311932 1805236 1695322 421846 Queensland Cairns 92273 1805236 1383390 514119 Queensland Brisbane 1291117 1805236 1291117 1805236 South Australia Adelaide 978100 978100 978100 978100 Tasmania Hobart 126118 126118 126118 126118 Victoria Melbourne 2865329 2990711 2990711 2865329 Victoria Geelong 125382 2990711 125382 2990711 West Australia Perth 1096829 1096829 1096829 1096829
In this example, both win2 and win3 refer to win1, while also having their own individual ORDER BY clauses.
However, a window component defined in one window cannot be redefined by another window referencing it.
Also, cyclic references and using multiple window references in a single window are not allowed.