patternsqlMinor

Window Functions: What is the purpose of Rows Unbounded Preceeding?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowsfunctionsthepreceedingwhatunboundedwindowpurpose

Problem

What is the purpose of using the Rows Unbounded Preceeding clause in a window function?
I think I understand that it's basically saying not to limit how far back to look when rolling up for the aggregate function, but how is that different than not using the clause at all?

Could you please provide an example to illustrate the difference between:

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC ROWS UNBOUNDED PRECEEDING)

And

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC)

Note: My question is in the context with no cap on the Rows Following.

Solution

Semantic Differences Between Rows and Range

If we look at the results of this query, it's fairly easy to see when we might want to examine use range or rows.

SELECT   OwnerUserId,
         CAST(CreationDate AS DATE) AS DumbedDownDate,
         Score,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE)) AS Not_Specified,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE) RANGE UNBOUNDED PRECEDING ) AS Range_Specified,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE) ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId = 1
AND      CAST(CreationDate AS DATE) BETWEEN '2008-08-01' AND '2008-08-31'
ORDER BY DumbedDownDate;

When no boundary is set, the results are the same as if we specifically ask for a RANGE. Asking for ROWS gives us a different result all together.

The ROWS result is more like a running total, and the RANGE result is the total value for the... range of rows.

Performance

If you use rows, the window spool operator can use an in memory work table. Range uses an on disk work table, which is sometimes slower.

Compare these two queries:

SELECT   OwnerUserId,
         CreationDate,
         Score,
         SUM(Score) 
             OVER ( ORDER BY CreationDate RANGE UNBOUNDED PRECEDING ) AS Range_Specified
FROM     dbo.Posts
WHERE    OwnerUserId = '20080101' 
AND      CreationDate = '20080101' 
AND      CreationDate < '20150101'
ORDER BY CreationDate;

The query plans for them show quite different timing for the Window Spool:

If we look at the output of STATISTICS IO for both queries, they're also quite different:

Table 'Worktable'. Scan count 1363952, logical reads 11944334, physical reads 0, 
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4722714, physical reads 0, read-ahead 
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

vs.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4723890, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Batch Mode

In SQL Server 2019, where Batch Mode is generally available for Row Store queries, or SQL Server 2016/2017 where there are various methods to introduce Batch Mode processing to Row Store queries, it's possible to get close to even performance thanks to the addition of the Batch Mode Window Aggregate.

See the three part series from Mr. Itzik Ben-Gan here:

Part 1

Part 2

Part 3

Code Snippets

SELECT   OwnerUserId,
         CAST(CreationDate AS DATE) AS DumbedDownDate,
         Score,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE)) AS Not_Specified,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE) RANGE UNBOUNDED PRECEDING ) AS Range_Specified,
         SUM(Score) OVER 
                   ( ORDER BY CAST(CreationDate AS DATE) ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId = 1
AND      CAST(CreationDate AS DATE) BETWEEN '2008-08-01' AND '2008-08-31'
ORDER BY DumbedDownDate;

SELECT   OwnerUserId,
         CreationDate,
         Score,
         SUM(Score) 
             OVER ( ORDER BY CreationDate RANGE UNBOUNDED PRECEDING ) AS Range_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101' 
AND      CreationDate < '20150101'
ORDER BY CreationDate;


SELECT   OwnerUserId,
         CreationDate,



         Score,
         SUM(Score) 
             OVER ( ORDER BY CreationDate ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101' 
AND      CreationDate < '20150101'
ORDER BY CreationDate;

Table 'Worktable'. Scan count 1363952, logical reads 11944334, physical reads 0, 
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4722714, physical reads 0, read-ahead 
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4723890, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Context

StackExchange Database Administrators Q#239168, answer score: 5

Revisions (0)

No revisions yet.