Two running totals can disagree without either query containing a typo. The difference in ROWS vs RANGE is whether a tied sort value joins its peers or advances one row at a time.
Start the ROWS vs RANGE Test With an Intentional Tie
The easiest way to understand a frame is to give it awkward data. Create two transactions on the same date. Each has its own identifier and amount. The identifier tells us which transaction we mean, while the date creates peers.
Run the examples in one SSMS session. The input values are a small teaching set, not production measurements. The temporary table disappears when the session closes. In a real ledger, match the ordering columns to the business sequence that defines the balance.
I check the desired result before looking at the execution plan. Should both transactions on one date show the closing daily balance? Or should each transaction show the balance immediately after that transaction? The answer chooses the frame. A faster wrong balance remains impressively wrong.
CREATE TABLE #Ledger
(
TransactionID int NOT NULL PRIMARY KEY,
PostingDate date NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT #Ledger VALUES
(1, '20260102', 10.00),
(2, '20260102', 20.00),
(3, '20260103', -5.00),
(4, '20260104', 7.00);
SELECT TransactionID, PostingDate, Amount
FROM #Ledger
ORDER BY PostingDate, TransactionID;RANGE Includes the Whole Peer Group
For a running SUM, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes earlier sort values and every peer of the current value. A peer has the same complete window ordering value. Here, both transactions dated January 2 share that value.
Consequently, each of those transactions gets the total through that entire date. The final presentation order still lists the individual transactions. It does not redefine which rows belong to the calculation. Window ordering and final output ordering have separate jobs.
SELECT TransactionID, PostingDate, Amount,
SUM(Amount) OVER
(
ORDER BY PostingDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ThroughDateAmount
FROM #Ledger
ORDER BY PostingDate, TransactionID;ROWS Advances One Position at a Time
ROWS uses positions within the ordered partition. For a transaction balance, include TransactionID in the window order. That supplies a unique sequence when dates tie. Otherwise, a row-by-row frame lacks a defined order between those peers.
Do not rely on the outer ORDER BY to repair an incomplete window order. The calculation happens before the output is arranged. Add the tie breaker inside OVER, where it controls the frame. If transaction identifiers do not reflect the required business sequence, use the approved sequence instead.
SELECT TransactionID, PostingDate, Amount,
SUM(Amount) OVER
(
ORDER BY PostingDate, TransactionID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS AfterTransactionAmount
FROM #Ledger
ORDER BY PostingDate, TransactionID;The Omitted Frame Picks RANGE Over ROWS
For a frame-aware aggregate with ORDER BY, SQL Server defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when the frame is omitted. A shorter query therefore chooses peer-group behavior. It does not choose whatever running balance you intended.
This default concerns functions that accept a frame. Ranking functions have their own rules. Do not paste a frame onto ROW_NUMBER and expect the same syntax to work. Also, a window without ORDER BY gives an aggregate the entire partition rather than an ordered running total.
Use ROWS vs RANGE as a review question whenever somebody writes a running aggregate. The following side-by-side query makes the omitted frame visible through comparison. Both expressions deliberately use the same date order. Keep the longer explicit expression when peer totals are the actual requirement.
SELECT TransactionID, PostingDate,
SUM(Amount) OVER (ORDER BY PostingDate) AS DefaultFrameAmount,
SUM(Amount) OVER
(
ORDER BY PostingDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ExplicitPeerAmount
FROM #Ledger
ORDER BY PostingDate, TransactionID;
Add a Partition Without Mixing Accounts
A running total usually belongs to an account, customer, or product. PARTITION BY restarts the calculation for each such group. It also keeps unrelated rows out of the frame. Ordering alone cannot supply that boundary.
If every account begins on the same date, peers still remain inside their own partitions. Include an account identifier in the final output so the reader can see the grouping. Keep filters deliberate. Filtering out older transactions before the window calculation also removes their contribution to the opening balance.
I ask about opening balances whenever a report starts at an arbitrary date. A correct frame cannot reconstruct rows already excluded by WHERE. Calculate the running balance over the necessary history, then apply the display-date filter in an outer query when that is the intended rule.
Worktables Explain the ROWS vs RANGE Speed Gap
In traditional row-mode Window Spool plans, a RANGE frame uses an on-disk worktable to manage peer groups. ROWS with an unbounded preceding frame supports a more efficient running-aggregate path with an in-memory worktable. That difference can appear as Worktable logical reads under STATISTICS IO.
The reason is practical. The number of rows sharing an ordering value has no fixed bound. Managing the peer boundary requires a different implementation from advancing one position. For a discrete transaction balance, an explicit ROWS frame avoids unnecessary peer processing and can run faster.
Recent versions also use different operators and batch-mode execution. Do not promise a particular worktable pattern for every plan. Inspect the actual plan and Messages output on the target system. Logical reads describe page access, not a claim that every page came from physical storage.
Measure Comparable Calculations
Use a unique ordering column to compare performance without changing results. The next pair uses TransactionID for both frames. This makes every peer group a single row. Enable the actual execution plan in SSMS, then inspect the operators and STATISTICS IO messages. Even on the four-row teaching table, the Messages tab shows Worktable logical reads for the first query and none for the second.
The small teaching table is for syntax and meaning. For a meaningful performance comparison, repeat the pair against an appropriately sized test ledger. Keep filters, partitions, returned columns, and ordering identical. Run both more than once under comparable conditions, without clearing shared caches on a production server.
SET STATISTICS IO ON;
SELECT TransactionID,
SUM(Amount) OVER
(
ORDER BY TransactionID
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RangeBalance
FROM #Ledger
ORDER BY TransactionID;
SELECT TransactionID,
SUM(Amount) OVER
(
ORDER BY TransactionID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RowsBalance
FROM #Ledger
ORDER BY TransactionID;
SET STATISTICS IO OFF;Write the ROWS vs RANGE Choice Into Every Frame
Write the frame explicitly every time a frame-aware calculation depends on it. Use RANGE for peer-boundary semantics and ROWS for position-based semantics. Add a unique order when each individual row needs a stable position. A comment about the business rule makes review easier.
SQL Server does not support numeric preceding offsets with RANGE. A moving seven-day total therefore needs a suitable date-based design, not RANGE 6 PRECEDING copied from another dialect. Test missing dates as well as duplicates.
Resolve ROWS vs RANGE with a tied-value test before tuning. Then measure the chosen expression on your own server. Correctness defines the result, and the plan explains the cost of obtaining it.
Related reading on this blog: Percent of Total With SUM() OVER() in One Query and Moving Averages in T-SQL.
A window frame is not decoration, it is the boundary of the calculation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
