SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 over ROWS vs. over RANGE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TG
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 01/30/2013 :  14:50:24  Show Profile  Reply with Quote
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

Edited by - TG on 01/30/2013 15:00:07

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 01/30/2013 :  15:18:43  Show Profile  Visit russell's Homepage  Reply with Quote
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

Edited by - russell on 01/30/2013 15:19:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000