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