The WINDOW Clause: Name a Window Once and Reuse It

Copying the same partition and sort rule four times invites one quiet mismatch. The WINDOW clause names that rule once, so related calculations share the intended sequence.

Check the Version and Compatibility Level

SQL Server 2022 introduced named windows. The database needs compatibility level 160 or higher. A SQL Server 2025 instance can still host a database at an older level, so check the database rather than assuming the instance version is enough.

The next query reads those settings without changing anything. Test compatibility changes through your normal database process before adopting the new syntax. A parser that does not support WINDOW will reject the query even when an earlier conditional branch was supposed to avoid executing it.

I check compatibility before reviewing a named-window query that fails immediately. It is faster than arguing with punctuation that was correct all along. Syntax availability and query logic are separate checks. Once the environment supports the feature, the remaining examples run in one SSMS session.

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
    DB_NAME() AS DatabaseName, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Give the Repeated Rule Something to Calculate

Use a small ledger with customer identifiers, transaction identifiers, dates, and amounts. Two transactions share a date, so the ordering rule needs a tie breaker. Each customer's calculation restarts independently. These are deliberate input values for illustrating the expression.

Create the temporary table once. Run each later query against it in the same session. The business rule is a balance after each transaction, ordered by posting date and then transaction identifier. Confirm that identifiers actually provide the required sequence before using that rule in a real ledger.

CREATE TABLE #WindowLedger
(
    CustomerID int NOT NULL,
    TransactionID int NOT NULL,
    PostingDate date NOT NULL,
    Amount decimal(12,2) NOT NULL,
    PRIMARY KEY (CustomerID, TransactionID)
);
INSERT #WindowLedger VALUES
(10, 1, '20260102', 10.00),
(10, 2, '20260102', 20.00),
(10, 3, '20260103', -5.00),
(20, 4, '20260102', 12.00),
(20, 5, '20260104', 8.00);

Read the Before Query as a Reviewer

The first form repeats PARTITION BY, ORDER BY, and the frame. SUM, AVG, and COUNT_BIG all use exactly the same row set. That makes the expression correct, but gives future edits three places to drift.

A missing TransactionID in one expression changes tie behavior. A missing frame changes the default semantics. A different partition mixes different customers. Those mistakes can hide among correct-looking copied clauses, especially after a report gains another metric.

Which parts of these calculations are supposed to stay identical? Name that shared contract rather than choosing the longest expression merely because it repeats. The amount functions are different. Their membership and order rules are deliberately the same.

SELECT CustomerID, TransactionID, PostingDate, Amount,
    SUM(Amount) OVER
    (PARTITION BY CustomerID ORDER BY PostingDate, TransactionID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAmount,
    AVG(Amount) OVER
    (PARTITION BY CustomerID ORDER BY PostingDate, TransactionID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAverage,
    COUNT_BIG(*) OVER
    (PARTITION BY CustomerID ORDER BY PostingDate, TransactionID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TransactionsSoFar
FROM #WindowLedger
ORDER BY CustomerID, PostingDate, TransactionID;

Name the Running Window in the WINDOW Clause

The equivalent query defines RunWindow once after FROM. Each aggregate refers to that name in OVER. The outer ORDER BY still controls output presentation. A named window defines calculation rules, not a stored result or guaranteed output sort.

Read the definition as a shared promise. It partitions by customer, orders each partition uniquely, and uses an explicit row-based running frame. The functions still perform their own calculations. Reusing the window name does not turn the three outputs into one aggregate value.

The WINDOW clause reduces repeated text without claiming a performance improvement. The optimizer still chooses the execution plan. Compare actual plans and measured work if performance is part of the change request. Clearer SQL is already useful, but it does not come with free stopwatch results.

SELECT CustomerID, TransactionID, PostingDate, Amount,
    SUM(Amount) OVER RunWindow AS RunningAmount,
    AVG(Amount) OVER RunWindow AS RunningAverage,
    COUNT_BIG(*) OVER RunWindow AS TransactionsSoFar
FROM #WindowLedger
WINDOW RunWindow AS
(
    PARTITION BY CustomerID
    ORDER BY PostingDate, TransactionID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY CustomerID, PostingDate, TransactionID;

Chain Named Windows Inside One WINDOW Clause

Sometimes only part of the rule is shared. Define CustWindow for partitioning. Extend it with posting order to create SeqWindow. Then extend that ordered window with a frame to create RunWindow. Each level adds a missing component.

The next query uses the partition-only window for a full customer total. It uses the ordered window for ROW_NUMBER, which does not accept an aggregate frame. The running aggregate uses the final framed window. One definition should not be stretched across functions with incompatible syntax requirements.

Do not redefine an inherited PARTITION BY, ORDER BY, or frame. If the required rule differs, create an independent window instead. References cannot form a cycle. Keep the names descriptive enough that a reviewer understands the inheritance without mentally expanding several anonymous letters.

SELECT CustomerID, TransactionID, PostingDate, Amount,
    SUM(Amount) OVER CustWindow AS CustomerTotal,
    ROW_NUMBER() OVER SeqWindow AS TransactionPosition,
    SUM(Amount) OVER RunWindow AS RunningAmount
FROM #WindowLedger
WINDOW
    CustWindow AS (PARTITION BY CustomerID),
    SeqWindow AS (CustWindow ORDER BY PostingDate, TransactionID),
    RunWindow AS (SeqWindow ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY CustomerID, PostingDate, TransactionID;

Add a Frame Where the Function Uses It

You can also extend a named ordered window directly inside OVER. That is useful when two functions need the same partition and order but different frames. Define the shared components once, then state each frame beside the relevant function.

The following comparison shows a running total and a three-row moving average. Three rows means three transaction positions, not three calendar days. Missing dates do not create missing rows. Keep the metric's business meaning visible in its alias and surrounding explanation.

I write the frame explicitly even when a default would return the current answer. A later change that introduces ties should not silently redefine the metric. Named rules make review shorter only when they also make the intended calculation clearer.

SELECT CustomerID, TransactionID, PostingDate,
    SUM(Amount) OVER
        (SeqWindow ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAmount,
    AVG(Amount) OVER
        (SeqWindow ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeTransactionAverage
FROM #WindowLedger
WINDOW SeqWindow AS
    (PARTITION BY CustomerID ORDER BY PostingDate, TransactionID)
ORDER BY CustomerID, PostingDate, TransactionID;

Filtering Still Happens Before the Calculation

A WHERE predicate inside the window query removes rows before its functions run. If you filter away earlier transactions, their amounts disappear from the running balance. Naming the window does not change that order of operations.

Calculate the required history in a CTE, then filter displayed rows outside it. The named window belongs to that query's scope. Do not expect an outer SELECT to reuse a window name defined inside the CTE. Carry the calculated value out as a normal result column.

;WITH Balances AS
(
    SELECT CustomerID, TransactionID, PostingDate,
        SUM(Amount) OVER RunWindow AS RunningAmount
    FROM #WindowLedger
    WINDOW RunWindow AS
        (PARTITION BY CustomerID ORDER BY PostingDate, TransactionID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
SELECT CustomerID, TransactionID, PostingDate, RunningAmount
FROM Balances
WHERE PostingDate >= CONVERT(date, '20260103', 112)
ORDER BY CustomerID, PostingDate, TransactionID;

Use the WINDOW Clause Only for Rules That Match

Keep the before and after queries during review. Compare customer boundaries, tied dates, negative amounts, and the first row of each partition. Verify that each rewritten metric retains its own intended frame. Formatting improvements should not change the report's answers.

Avoid building a large inheritance chain just to eliminate every repeated word. One or two meaningful shared definitions are easier to maintain than a puzzle. Use the WINDOW clause when several calculations genuinely share partition and ordering rules.

That gives the next editor one clear place to inspect the shared contract. Separate windows still handle genuinely different metrics. The result is readable SQL whose calculation boundaries stay visible after another column is added.

Related reading on this blog: The Four Window Functions You Will Actually Use and How to Find Running Total in SQL Server.

A named window is not a cached answer, it is a reusable rule for a calculation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.