Monday, March 26, 2012

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

No comments:

Post a Comment