Windowing Enhancements New T-SQL Enhancement in SQL Server

SQL Server 2022 brings several significant T-SQL enhancements that streamline data manipulation, especially when working with analytics, window functions, and sliding aggregations. SQL Developers, Data Engineers, and Database Administrators can leverage these new features to simplify complex data operations, improve readability, and optimize query performance.

Key Enhancements in SQL Server 2022 Window Functions

1. OVER with ORDER BY for Aggregate Functions: Allows running and sliding aggregations.
2. Sliding Aggregations at Account Level: Maximize window rows and apply at a granular level.
3. WINDOW Clause: Reduces code duplication.
4. IGNORE NULLS: Optimizes data analysis for first and last values.
5. IGNORE VALUES for NULLs in Aggregations: Adds flexibility in handling NULL data points.

We will use the table below for our examples.

CREATE TABLE dbo.AccountTransactions (
    AccountID INT,
    TransactionDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO dbo.AccountTransactions (AccountID, TransactionDate, Amount)
VALUES 
    (1, '2023-01-01', 100.00),
    (1, '2023-01-02', 150.00),
    (1, '2023-01-03', 200.00),
    (1, '2023-01-04', NULL),
    (1, '2023-01-05', 300.00),
    (2, '2023-01-01', 500.00),
    (2, '2023-01-02', 700.00),
    (2, '2023-01-03', NULL),
    (2, '2023-01-04', 800.00),
    (2, '2023-01-05', 900.00);

1. Using OVER with ORDER BY for Aggregate Functions

In SQL Server 2012, Microsoft introduced the ability to use OVER with ORDER BY for aggregate functions, which allows for running and sliding aggregations. This feature lets you compute cumulative totals, moving averages, and other metrics across an ordered dataset without needing a self-join or correlated subquery.

Example. Running Total of Transactions by Account

The following query calculates a running total of transaction amounts per AccountID, ordered by TransactionDate.

SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS RunningTotal
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
The SUM function is paired with OVER (PARTITION BY AccountID ORDER BY TransactionDate). For each row, it calculates a running total of the Amount column by partitioning the data by AccountID and ordering it by TransactionDate.
*/

Output

Use Case: Running totals are helpful for calculating cumulative spending per account, which is common in financial reporting or customer analytics.

2. Sliding Aggregations with a Limit on Rows per Window

Sliding aggregations are a specific type of windowed calculation where only a fixed number of preceding or following rows are considered. In SQL Server 2022, this can be particularly useful for rolling averages or sums over a specified window of rows.

Example. Three-Row Sliding Sum.

In this example, we calculate a sliding sum of the amount for each of the three most recent transactions of each AccountID.

SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS 2 PRECEDING) AS SlidingSum
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
The ROWS 2 PRECEDING clause limits the window to the current row and the previous two rows. This is a rolling three-row sum, which helps in understanding recent trends in transaction amounts.
*/

Output

Use Case. Financial analysts might use a sliding aggregation to calculate moving averages, which is useful for smoothing out trends over a defined period

3. Using the WINDOW Clause to Eliminate Code Duplication

The WINDOW clause, introduced in SQL Server 2022, allows you to define a window frame once and reference it multiple times. This feature helps reduce code duplication and improves readability, especially in queries with multiple windowed calculations.

Example. Defining and Using a Window.

Here, we define a window once using the WINDOW clause and reference it for both the running total and the sliding sum.

SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER w AS RunningTotal,
    AVG(Amount) OVER w AS RollingAverage
FROM dbo.AccountTransactions
WINDOW w AS (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY AccountID, TransactionDate;

/*
The WINDOW clause defines a reusable window called w, which can then be applied to different aggregates, reducing code repetition and making modifications easier.
*/

Output

Use Case. The WINDOW clause is particularly valuable in analytics queries where multiple window functions need to use the same partition and order criteria.

4. Using IGNORE NULLS in FIRST_VALUE and LAST_VALUE Functions

The IGNORE NULLS option, introduced in SQL Server 2022, allows window functions like FIRST_VALUE and LAST_VALUE to skip NULL values, making it easier to retrieve non-null values in sequences with missing data.

Example. Getting the Last Non-NULL Value per Account.

In this example, we use LAST_VALUE with IGNORE NULLS to fetch the most recent non-null transaction amount for each account.

SELECT
    AccountID,
    TransactionDate,
    Amount,
    LAST_VALUE(Amount) IGNORE NULLS OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS LastNonNullAmount
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
Without IGNORE NULLS, the LAST_VALUE function would return a NULL value if the last row in the partition contained NULL. With IGNORE NULLS, SQL Server skips over the NULL values and returns the most recent non-null value instead.
*/

Output

Use Case. This is particularly useful for handling incomplete or intermittent data, such as filling in missing stock prices, temperature readings, or any sequential time-based metric.

5. Handling NULLs with the IGNORE NULLS Option for Aggregations

SQL Server 2022's IGNORE NULLS option can also be applied to functions like SUM and AVG, making it easier to handle datasets with missing values without affecting calculations.

Example. Average Transaction Amount Ignoring NULLs.

The following query calculates the average transaction amount for each account while ignoring NULL values.

SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
    NULLIF(COUNT(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) 
    AS AverageAmount
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
With IGNORE NULLS, the calculation ignores rows where Amount is NULL, providing a more accurate average.
*/

Output

Use Case: NULL values are common in datasets, and having the ability to ignore them directly in aggregation functions simplifies code and improves result accuracy.

Conclusion

SQL Server 2022 brings powerful enhancements to window functions, making them more flexible and capable of handling real-world scenarios in SQL development. From running totals and sliding windows to null handling with IGNORE NULLS, these features improve the clarity, efficiency, and precision of windowed calculations. By leveraging these new tools, SQL developers, data engineers, and DBAs can build more expressive and performant SQL queries, making SQL Server 2022 a strong choice for data-intensive applications.