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.
over ROWS vs. over RANGE
Posted - 01/30/2013 : 14:50:24
