SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table

Introduction

I have received few emails and comments about my post SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005. The main question was is this can be done using CTE? Absolutely! What about Performance? It is same! Please refer above mentioned article for history of paging

SQL Server 2005 Code Samples

SQL 2005 Paging Method Using Derived Table

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(Order BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

SQL 2005 Paging Method Using CTE

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;

WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(Order BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)

SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO   

Following Image of Execution Plan displays that the performance for both of them is same with regarding to each other in one batch. This MAY NOT be true when there is complex query in issue. For most of the time, it is will be same.

Points of Interest

Refer the original article here :

SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table

Reference:

Pinal Dave (http://www.SQLAuthority.com), SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005