How to use the New WINDOW Clause in SQL Server 2022

By:   |   Updated: 2022-11-14   |   Comments (2)   |   Related: More > 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).

example of row_number function

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;
query results

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;
query result - multiple windows

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;
error when referencing multiple windows

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;
error cyclic reference

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





get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


Article Last Updated: 2022-11-14

Comments For This Article




Tuesday, November 15, 2022 - 9:28:15 AM - Koen Verbeeck Back To Top (90687)
Hi Bob,

there's a tutorial about window functions:
https://www.mssqltips.com/sqlservertutorial/9121/sql-server-t-sql-window-functions-tutorial/

Regards,
Koen

Monday, November 14, 2022 - 9:43:40 AM - Bob Back To Top (90684)
Great article!

Can we get an article covering the OVER clause?














get free sql tips
agree to terms