over ROWS vs. over RANGE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

over ROWS vs. over RANGE

Author

Topic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-30 : 14:50:24

Does anyone see any reason to use RANGE?
EDIT: yes (answer below

)

I see from the documentation that ROWS allows for literal positive integers where as RANGE does not. But other than that it seems that anywhere you could use RANGE you can also use ROWS with the same result. Perhaps I'm missing something

I was looking at io statistics just to see if sql was smart enough to handle multiple OVER clauses in the same statement without separate scans for each one. I was pleasantly surprised that it seems to handle that pretty well.

However, I was surprised to see that scans and reads jumped significantly when RANGE was used instead of ROWS. The results were the same but the work involved seems to be quite different.

In all these cases RANGE seems to take a lot more scans and reads than does ROWS.

rows/range current row
rows/range unbounded preceding
rows/range between unbounded preceding and current row
rows/range between current row and unbounded following
rows/range between unbounded preceding and unbounded following


use master
set statistics io on

select count(*) over (order by id RANGE between current row and unbounded following)
from   sysobjects
order by id

EDIT:
Ok - I see what I was missing.

quote:
from books online over
The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame “RANGE … CURRENT ROW …” includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.

So it depends on the distinctness of your OVER ORDER BY columns. When order by sysobject.TYPE then the results will be different between ROWS and RANGE:


select count(*) over (order by type RANGE current row )
from   sysobjects
order by id

vs.


select count(*) over (order by type ROWS current row )
from   sysobjects
order by id

I guess I answered my own question.

ROWS is a window of rows relative to the current row whereas
RANGE is a window of values relative to the current row.

We'll need to be thoughtful when selecting between ROWS and RANGE depending the the results we want. But If the ORDER BY in your OVER clause represents distinct values then both will give the same results but ROWS will have better performance.

Be One with the Optimizer
TG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-01-30 : 15:18:43

bit of a difference

(2101 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 4284, physical reads 0, read-ahead reads 0
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0

(2101 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources