ROW_NUMBER() paging - CTE or subquery ?

For paging in SQL 2005, ROW_NUMBER() is recommended. But is it best practice to use that with a CTE or a Subquery ? I get the same query plan for the two examples below. One link I found suggests CTEs are preferred:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx

Thoughts on this ?

Thanks,

Andy Mackie

Code Snippet

USE AdventureWorks;

GO

--Using a CTE

WITH OrderedOrders AS

(

SELECT SalesOrderID, OrderDate,

ROW_NUMBER()OVER(ORDERBY OrderDate)AS'RowNumber'

FROM Sales.SalesOrderHeader

)

SELECT*

FROM OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;

--Using a subquery

SELECT*

FROM

(

SELECT SalesOrderID, OrderDate,

ROW_NUMBER()OVER(ORDERBY OrderDate)AS'RowNumber'

FROM Sales.SalesOrderHeader

) OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;

I think that no difference here. Because SQL Optimizer is very smart and it will generate same execution plan.sql

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.sql

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.

ROW_NUMBER is very slow on large tables

Hi all,
I thought that ROW_NUMBER is designed to handle paging on large tables.
However, when I test it on a table with 1,000,000 records, a simple select
with ROW_NUMBER hangs for more than 10 minutes.
Any idea what can be done?
My table is:
Events (ID, Date, Desc)
My query is:
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100You still need to have an index on the column ID. Also maybe you have
other issues such as blocking and the problem is not with the
row_number function.
Adi|||I think your query is wrong.
try this and let me know if it works
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events order by id asc
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100|||Thank you, but this query is just as slow as mine.
"Omnibuzz" wrote:

> I think your query is wrong.
> try this and let me know if it works
> SELECT * FROM
> (
> SELECT TOP 100 *, ROW_NUMBER()
> OVER (ORDER BY ID ASC) as RowNo
> FROM Events order by id asc
> ) as SortedEvents
> WHERE RowNo > 50 and RowNo < 100|||then can you tell me if this query is fast? do you have an index on ID?
SELECT * FROM
(
SELECT TOP 100 *
FROM Events order by id asc
) as SortedEvents|||Thank you.
ID has index.
Could you please elaborate on what is the blocking issue?
I'm testing on a standalone, development server.
Nobody else uses it.
"Adi" wrote:

> You still need to have an index on the column ID. Also maybe you have
> other issues such as blocking and the problem is not with the
> row_number function.
> Adi
>|||Anton,
try this:
SELECT Events .* FROM Events join
(
SELECT TOP 100 id, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
on events.id = SortedEvents.id
WHERE RowNo > 50 and RowNo < 100|||The nesting and the proprietary TOP 100 might be causing problems.
ROW_NUMBER () is new and probalby not well-optimized yet. Keep it
simple
SELECT event_id,
ROW_NUMBER()
OVER (ORDER BY event_id ASC) AS rn
FROM Events
WHERE rn BETWEEN 50 AND 100;|||Nonsense.Several years ago all db vendors acknowledged that all major
db problems had been solved (so they were free to add xml to the engine).
This is just another example of a user underming the operation of the db
by doing something silly and not informing the ng of exactly what it is.
'It's the user stupid' hangs on the wall of all vendors (and in the minds of
most responders:).
On a side note I still do not see any explanation from you from the mind
'set'
to windowing.So yesterdays criminial magically becomes todays most decorated
cop.
Code,code and nothing but code is STILL non-sense.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365856.484616.283960@.j33g2000cwa.googlegroups.com...
>.
> ROW_NUMBER () is new and probalby not well-optimized yet.
>.|||Steve Dassin wrote:
> Nonsense.Several years ago all db vendors acknowledged that all major
> db problems had been solved
Really? Please amuse me by posting an example of some vendor making
such a claim. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--