SQL Server Window Frames: ROWS vs RANGE, UNBOUNDED and CURRENT ROW (2026)

1. What Is a Window Frame?

A window function calculates a result for each row by looking at a set of rows called its window. The OVER() clause defines that window in up to three layers:

function_name()  OVER (
    [ PARTITION BY partition_expression [, ...] ]
    [ ORDER BY     sort_expression      [, ...] [ASC|DESC] ]
    [ ROWS | RANGE
        BETWEEN frame_start AND frame_end ]
)

• PARTITION BY — splits the result set into independent groups. The function resets for each new partition.
• ORDER BY — controls the sort order inside each partition and is required when a frame clause is used.
• Frame clause — the optional third layer that restricts the window to a sliding subset of the ordered partition. This is what this article is about.

Without a frame clause, the window covers the entire partition (or the current partition up to the current sort value — the dangerous default described in Section 11). The frame clause lets you say things like "only look at the five rows before and after me" or "look from the beginning of the partition to my row".

2. Frame Units: ROWS vs RANGE — The Critical Difference

The first keyword in a frame clause is either ROWS or RANGE. They sound similar but behave very differently when the ORDER BY column contains duplicate values.

ROWS: Physical Row Offsets

ROWS counts actual rows. Each row is treated as a distinct entity regardless of whether it shares an ORDER BY value with a neighbour. The frame boundary is always a fixed number of physical rows before or after the current row.

RANGE: Logical Value Ranges

RANGE works on values. All rows that have the same ORDER BY value as the boundary row are included together. When you write RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, "CURRENT ROW" means every row with the same ORDER BY value as this row — not just this physical row.

Tied-Values Demo

Consider a simple sales table with two rows on the same date:

CREATE TABLE #Sales (
    SaleDate  date,
    Amount    decimal(10,2)
);
INSERT INTO #Sales VALUES
    ('2026-01-01', 100),
    ('2026-01-02', 200),
    ('2026-01-02', 150),   -- same date as previous row
    ('2026-01-03', 300);

-- ROWS running total
SELECT
    SaleDate,
    Amount,
    SUM(Amount) OVER (ORDER BY SaleDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningRows,
    SUM(Amount) OVER (ORDER BY SaleDate
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningRange
FROM #Sales
ORDER BY SaleDate, Amount;

Result:

With ROWS, the first 2026-01-02 row gets a running total of 250 (100+150) and the second gets 450 (100+150+200) — each row is counted independently. With RANGE, both 2026-01-02 rows receive 450 because CURRENT ROW in RANGE mode means all rows tied at this ORDER BY value, so both dates are included together.

3. ROWS Mode: Physical Row Offsets

In ROWS mode, SQL Server counts physical rows from a reference point. The reference point can be:

• The start of the partition (UNBOUNDED PRECEDING)
• N rows before the current row (N PRECEDING)
• The current row itself (CURRENT ROW)
• N rows after the current row (N FOLLOWING)
• The end of the partition (UNBOUNDED FOLLOWING)

ROWS mode produces deterministic, predictable results at every row, makes the engine's internal spool-free plan easy to produce, and avoids the surprising "all ties included" behaviour of RANGE. It should be your default choice.

4. RANGE Mode: Logical Value Ranges and the Duplicate Trap

RANGE mode treats all rows with the same ORDER BY value as a single logical unit. The most common manifestation of this is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is SQL Server's default frame when you write an ORDER BY without an explicit frame clause (see Section 11).

When RANGE Causes Unexpected Results

-- Scenario: daily revenue with multiple transactions per day
CREATE TABLE #DailyTx (
    TxDate    date,
    TxID      int,
    Revenue   decimal(10,2)
);
INSERT INTO #DailyTx VALUES
    ('2026-03-01', 1, 500),
    ('2026-03-01', 2, 300),
    ('2026-03-02', 3, 400),
    ('2026-03-03', 4, 200),
    ('2026-03-03', 5, 600);

-- RANGE: produces the same "running total" for every row on the same date
SELECT
    TxDate, TxID, Revenue,
    SUM(Revenue) OVER (ORDER BY TxDate
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningRANGE
FROM #DailyTx
ORDER BY TxDate, TxID;

Output:

Both 2026-03-01 rows show 800 — the full day total — not a sequential accumulation of 500 then 800. Switch to ROWS and you get 500, 800, 1200, 1400, 2000 — an honest row-by-row running total.

5. Frame Boundaries: Complete Reference

The full frame syntax is:

{ ROWS | RANGE } BETWEEN <frame_start> AND <frame_end>

Additionally, N PRECEDING and N FOLLOWING with RANGE only work when the ORDER BY column is numeric or datetime and N is a compatible literal. The far more common use is with ROWS.

6. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — Running Total

The most common window frame pattern: accumulate from the start of the partition up to and including the current row. This is the classic cumulative sum or running total.

-- Running revenue total per product, ordered by sale date
SELECT
    ProductID,
    SaleDate,
    Revenue,
    SUM(Revenue) OVER (
        PARTITION BY ProductID
        ORDER BY     SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal,
    COUNT(*)     OVER (
        PARTITION BY ProductID
        ORDER BY     SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS SaleNumber
FROM Sales.Transactions
ORDER BY ProductID, SaleDate;

Each row shows the cumulative revenue for that product from day one up to the current sale. COUNT(*) with the same frame gives the sequential sale number within the product — a cheaper alternative to ROW_NUMBER() when you also need the running sum.

7. ROWS BETWEEN N PRECEDING AND CURRENT ROW — Rolling Window & Moving Average

Replace UNBOUNDED PRECEDING with a fixed number to create a sliding window that covers only the last N rows (including the current one). This is perfect for 7-day moving averages, 30-day rolling revenue, or any metric that should "forget" history beyond a fixed lookback.

-- 7-day rolling average of daily page views
-- Note: "7 PRECEDING" means the 6 rows before + current row = 7 rows total
SELECT
    PageDate,
    PageViews,
    AVG(PageViews * 1.0) OVER (
        ORDER BY PageDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS Avg7Day,
    SUM(PageViews) OVER (
        ORDER BY PageDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS Sum7Day
FROM Analytics.DailyStats
ORDER BY PageDate;

At the start of the dataset where fewer than 6 prior rows exist, SQL Server computes the average over however many rows are available. If you need a minimum-rows guard, wrap the result in a CASE or filter with a COUNT(*) OVER (…) threshold:

-- Only show moving average once we have at least 7 data points
SELECT
    PageDate,
    PageViews,
    CASE
        WHEN ROW_NUMBER() OVER (ORDER BY PageDate) >= 7
        THEN AVG(PageViews * 1.0) OVER (
                 ORDER BY PageDate
                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        ELSE NULL
    END AS Avg7Day
FROM Analytics.DailyStats
ORDER BY PageDate;

8. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — Reverse Running Total

Flip the frame so it starts at the current row and extends to the end of the partition. The result is a reverse cumulative sum — what remains after the current row, including the current row itself. Useful for "remaining balance", "items left to ship", or "time left in sprint".

-- Remaining order value still to be fulfilled, ordered by priority
SELECT
    OrderID,
    Priority,
    OrderValue,
    SUM(OrderValue) OVER (
        ORDER BY     Priority
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS RemainingBacklog,
    SUM(OrderValue) OVER () AS TotalBacklog
FROM Sales.Orders
WHERE Status = 'Pending'
ORDER BY Priority;

The first row shows the entire backlog (current + all following). The last row shows only that order's value. Dividing RemainingBacklog by TotalBacklog gives a percentage-remaining metric without a subquery or self-join.

9. ROWS BETWEEN N PRECEDING AND N FOLLOWING — Centred Moving Average

A centred window looks N rows backward and N rows forward, placing the current row in the middle. This produces a smoother moving average because it uses future context. It is common in financial charting and signal processing.

-- Centred 5-point moving average (2 before, current, 2 after)
SELECT
    MeasureDate,
    SensorValue,
    AVG(SensorValue * 1.0) OVER (
        ORDER BY MeasureDate
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS CentredAvg5,
    -- compare with trailing 5-point average
    AVG(SensorValue * 1.0) OVER (
        ORDER BY MeasureDate
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS TrailingAvg5
FROM IoT.SensorReadings
ORDER BY MeasureDate;

At the first two rows, SQL Server automatically clips the window to whatever is available (it does not pad with NULLs or zeros). The centred average for the very first row is therefore an average of only three values: it + the two following rows.

10. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — Grand Total in Every Row

Extend the frame to cover the entire partition in both directions and you get the partition total in every row. This is equivalent to SUM(col) OVER (PARTITION BY …) with no frame clause, but it is more explicit and communicates intent clearly.

-- Each row shows its value, the department total, and a percentage
SELECT
    EmployeeID,
    DepartmentID,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY DepartmentID
        ORDER BY     EmployeeID
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS DeptTotal,
    CAST(Salary * 100.0 /
         SUM(Salary) OVER (
             PARTITION BY DepartmentID
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS decimal(5,2)) AS PctOfDept
FROM HR.Employees
ORDER BY DepartmentID, Salary DESC;

No subquery, no GROUP BY, no self-join. The second SUM() OVER() omits ORDER BY entirely — when there is no ORDER BY in the OVER(), the default frame is automatically the entire partition, so the explicit frame clause is not needed there.

11. Default Frame Behaviour: The Invisible RANGE Trap

This is the most important thing to memorise about window frames. SQL Server applies a default frame whenever you write ORDER BY inside OVER() but omit the frame clause:

The dangerous case is the middle row. When you write:

-- Looks like a running total — but is it?
SUM(Revenue) OVER (ORDER BY SaleDate)

SQL Server silently applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. As shown in Section 4, if SaleDate has ties, every row on the same date gets the same accumulated total — not a row-by-row running total. This is legal, intentional SQL Server behaviour, but it catches many developers by surprise.

12. Performance: ROWS vs RANGE Execution Plan Differences

Window frames have a direct impact on query plan cost. Understanding the plan operators helps you tune slow window queries.

ROWS Mode Plan

SQL Server typically implements ROWS mode with a Window Spool operator in a single ordered scan. The engine maintains a running accumulator as it moves through the rows. Memory requirement is O(1) — constant regardless of partition size. In SQL Server 2019+ with batch mode on rowstore enabled, many ROWS frames compile to a highly efficient batch-mode window aggregate operator.

RANGE Mode Plan

RANGE mode with CURRENT ROW as a boundary forces SQL Server to group tied rows together. This typically requires a sort operator plus a Segment operator to detect group boundaries. The plan may include an on-disk spool if the partition is large, driving up memory grants and tempdb usage.

Reading the Plan

-- Enable actual execution plan, then run:
SET STATISTICS IO, TIME ON;

SELECT
    SaleDate, Amount,
    SUM(Amount) OVER (ORDER BY SaleDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RowsRunning,
    SUM(Amount) OVER (ORDER BY SaleDate
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RangeRunning
FROM Sales.Transactions;

SET STATISTICS IO, TIME OFF;

Look for:

• Window Aggregate — the efficient batch-mode operator for ROWS (SQL Server 2019+)
• Window Spool — row-mode fallback; still efficient for ROWS with UNBOUNDED boundaries
• Sort + Segment + Sequence Project — the expensive RANGE pattern; watch for high estimated memory grants

13. Combining Multiple Window Functions with Different Frames

You can mix window functions with completely different frames in a single SELECT. SQL Server resolves each OVER() independently. The query below computes four different window aggregates in one pass — no subqueries, no CTEs, no self-joins:

SELECT
    SaleDate,
    StoreID,
    DailySales,

    -- 1. Running total from start of store's history
    SUM(DailySales) OVER (
        PARTITION BY StoreID
        ORDER BY     SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CumulativeSales,

    -- 2. 7-day trailing moving average
    AVG(DailySales * 1.0) OVER (
        PARTITION BY StoreID
        ORDER BY     SaleDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS Avg7Day,

    -- 3. Same day last week (1 row back in a 7-row window — requires 7 rows)
    LAG(DailySales, 7) OVER (
        PARTITION BY StoreID
        ORDER BY     SaleDate
    ) AS SameDayLastWeek,

    -- 4. Store's all-time average (entire partition, no ORDER BY)
    AVG(DailySales * 1.0) OVER (
        PARTITION BY StoreID
    ) AS AllTimeAvg

FROM Retail.StoreSales
ORDER BY StoreID, SaleDate;

Notice that LAG() uses no frame clause (offset functions don't need one), and the all-time average uses no ORDER BY in its OVER(), which defaults to the full partition automatically.

14. Practical Use Case: Loan Amortisation Schedule

Window frames shine in financial calculations where each row depends on the previous one. A loan amortisation schedule — opening balance, interest, payment, closing balance — is a classic example. Traditionally this requires a cursor or recursive CTE. With window functions it becomes a single clean query.

-- Setup: loan payment schedule table
CREATE TABLE #LoanPayments (
    PaymentNum   int,
    PaymentDate  date,
    Payment      decimal(12,2),
    InterestRate decimal(6,4)   -- monthly rate, e.g. 0.005 for 0.5%
);

INSERT INTO #LoanPayments VALUES
    (1, '2026-01-01', 1200.00, 0.005),
    (2, '2026-02-01', 1200.00, 0.005),
    (3, '2026-03-01', 1200.00, 0.005),
    (4, '2026-04-01', 1200.00, 0.005),
    (5, '2026-05-01', 1200.00, 0.005),
    (6, '2026-06-01', 1200.00, 0.005);

DECLARE @Principal decimal(12,2) = 7000.00;

-- Step 1: compute cumulative payments
-- Step 2: derive balances using window sums
WITH Amort AS (
    SELECT
        PaymentNum,
        PaymentDate,
        Payment,
        InterestRate,
        -- Total principal repaid up to (but not including) this payment
        @Principal
            - SUM(Payment) OVER (
                ORDER BY PaymentNum
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
              ) AS OpeningBalance
    FROM #LoanPayments
)
SELECT
    PaymentNum,
    PaymentDate,
    ISNULL(OpeningBalance, @Principal)               AS OpeningBalance,
    ROUND(ISNULL(OpeningBalance, @Principal)
          * InterestRate, 2)                          AS InterestCharged,
    Payment                                           AS PaymentMade,
    ISNULL(OpeningBalance, @Principal)
        + ROUND(ISNULL(OpeningBalance, @Principal)
                * InterestRate, 2)
        - Payment                                     AS ClosingBalance
FROM Amort
ORDER BY PaymentNum;

The key frame is ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING: for each payment row, it sums all payments before the current one to derive the opening balance. The first row has no prior payments so the sum is NULL, which we replace with the original principal using ISNULL.

Result Preview

* Simplified for illustration — actual figures depend on whether prior-row closing balance or raw principal deduction is used.