ROW_NUMBER() paging - CTE or subquery ?

Transact-SQL

Andrew Mackie


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 (ORDER BY 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 (ORDER BY OrderDate) AS 'RowNumber'

FROM Sales.SalesOrderHeader

) OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;



Top

Re: ROW_NUMBER() paging - CTE or subquery ?

Konstantin Kosinsky


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



Top