Problem
In this tip, we’ll introduce you to the new WINDOW clause introduced in SQL Server 2022. Its purpose is to simplify queries with multiple OVER clauses defined, which are used in window functions.
Solution
Note:at the time of writing, SQL Server 2022 is still in preview (the RC0 preview was used to write this tip). This means functionality or features of SQL Server might change, disappear, or be added in the final release.
When using a window function, you have to define an OVER clause. This clause specifies the partitioning and the ordering of the window, and in the case of certain functions, you can specify additional properties. Let’s illustrate with the ROW_NUMBER function:
SELECT
object_id
,name
,ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY name)
FROM sys.all_columns;
In this query, we’re numbering the rows returned by the query. The numbering restarts for each new object ID (a table), and the ordering is alphabetically by name (the column names).
The specification declared in the OVER clause defines how the window function must behave. Using the new WINDOW clause, we can rewrite this SQL statement:
SELECT
object_id
,name
,ROW_NUMBER() OVER win
FROM sys.all_columns
WINDOW win AS (PARTITION BY object_id ORDER BY name);
This will return the same result set as the previous query. The WINDOW clause is specified after the HAVING clause and before the ORDER BY in a SELECT statement:
SELECT
FROM
WHERE
GROUP BY
HAVING
WINDOW
ORDER BY
Let’s find out why we would want to use the new clause.
Using the WINDOW clause
Multiple Identical OVER Clauses
If you have only one window function in your SELECT query, it doesn’t make sense to specify a separate WINDOW clause. But if you have multiple window functions using the same OVER clause, you can simplify the query by defining one single WINDOW. Let’s expand our original query:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY name)
,PreviousColumnName = LAG(name) OVER (PARTITION BY object_id ORDER BY name)
,FirstColumn = FIRST_VALUE(name) OVER (PARTITION BY object_id ORDER BY name)
FROM sys.all_columns;
With the new WINDOW clause, we can do the following:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER win
,PreviousColumnName = LAG(name) OVER win
,FirstColumn = FIRST_VALUE(name) OVER win
FROM sys.all_columns
WINDOW win AS (PARTITION BY object_id ORDER BY name);
The query is more readable and doesn’t span as far to the right. Some OVER clauses can get pretty long, especially if many columns are involved in the partitioning or ordering.
Different OVER Clauses
It’s also possible to define multiple windows:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER winasc
,PreviousColumnName = LAG(name) OVER winasc
,NextColumnName = LAG(name) OVER windesc
,FirstColumn = FIRST_VALUE(name) OVER winasc
FROM sys.all_columns
WINDOW winasc AS (PARTITION BY object_id ORDER BY name ASC)
,windesc AS (PARTITION BY object_id ORDER BY name DESC)
ORDER BY OBJECT_ID, RID;
Windows Referencing Other Windows
A window defined in the WINDOW clause can reference another window. This makes it possible to define a “base window,” and expand it in other windows. Let’s take the query from the previous section. We can rewrite it so that both windows use a common window for the partitioning:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER winasc
,PreviousColumnName = LAG(name) OVER winasc
,NextColumnName = LAG(name) OVER windesc
,FirstColumn = FIRST_VALUE(name) OVER winasc
FROM sys.all_columns
WINDOW base AS (PARTITION BY object_id)
,was AS (base ORDER BY name ASC)
,windesc AS (base ORDER BY name DESC)
ORDER BY OBJECT_ID, RID;
The order in which you define the windows doesn’t matter. The following query will produce the same output:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER winasc
,PreviousColumnName = LAG(name) OVER winasc
,NextColumnName = LAG(name) OVER windesc
,FirstColumn = FIRST_VALUE(name) OVER winasc
FROM sys.all_columns
WINDOW winasc AS (base ORDER BY name ASC)
,base AS (PARTITION BY object_id)
,windesc AS (base ORDER BY name DESC)
ORDER BY OBJECT_ID, RID;
However, it’s not possible to reference multiple windows in the same window definition (not in the WINDOW clause or the SELECT clause). The following query will result in an error:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER winasc
,PreviousColumnName = LAG(name) OVER winasc
,NextColumnName = LAG(name) OVER windesc
,FirstColumn = FIRST_VALUE(name) OVER winasc
FROM sys.all_columns
WINDOW base AS (PARTITION BY object_id)
,winorder AS (ORDER BY name ASC)
,test AS (base winorder)
,windesc AS (base ORDER BY name DESC)
ORDER BY OBJECT_ID, RID;
You must also make sure any window references are not cyclic. For example, the following query will throw an error as well:
SELECT
object_id
,name
,RID = ROW_NUMBER() OVER winasc
,PreviousColumnName = LAG(name) OVER winasc
,NextColumnName = LAG(name) OVER windesc
,FirstColumn = FIRST_VALUE(name) OVER winasc
FROM sys.all_columns
WINDOW base AS (windesc)
,winasc AS (base ORDER BY name ASC)
,windesc AS (base ORDER BY name DESC)
ORDER BY OBJECT_ID, RID;
Conclusion
The new WINDOW clause doesn’t bring new functionality to SQL Server, but it does allow you to write queries with many OVER clauses in a more readable and efficient way. It’s possible to define multiple windows in a single query. Windows can also reference each other as long as the reference chain is not cyclic and there’s only one reference per window definition.
Next Steps
- Stay tuned for more SQL Server 2022 tips on the MSSQLTips website!
- For more information about window functions, check out the tutorial: SQL Server T-SQL Window Functions Tutorial.
- Another addition to the window functions in SQL Server 2022 is the IGNORE NULLS option. You can read about it in the tip: How To Retrieve the Last Non-NULL Value in SQL Server.
- You can find more SQL Server 2022 tips in this overview.
