SQL Server 2022 WINDOW Clause to Simplify SQL Queries

By: Koen Verbeeck | Updated: 2022-11-14 | Comments (5) | Related: > SQL Server 2022

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.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-14

How to use the New WINDOW Clause in SQL Server 2022