Journey to SQL Authority with Pinal Dave

I was doing paging in SQL Server 2000 using Temp Table or Derived Tables. I decided to checkout new function ROW_NUMBER() in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. I have compared both the following query on SQL Server 2005.

SQL 2005 Paging Method

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 2000 Paging Method

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

CREATE TABLE #tables (RowNumber INT IDENTITY(1,1),
                FirstName VARCHAR(100), LastName VARCHAR(100),
                EmailAddress VARCHAR(100))
INSERT INTO #tables (FirstName, LastName, EmailAddress)
    SELECT    PC.FirstName, PC.LastName, PC.EmailAddress
    FROM    Person.Contact PC
    ORDER BY FirstName, LastName, EmailAddress

SELECT    FirstName, LastName, EmailAddress
    FROM    #tables
    WHERE    RowNumber > @StartRow 
        AND RowNumber < @EndRow
DROP TABLE #tables
GO

While running both the query at same time in query analyzer and comparing execution plan I have discovered that SQL 2005 query method cost is 46% and SQL 2000 query method cost is 54%. Looking at client Statistics I noticed significant improvement in Time Statistics.

Update (6/11/2007) : Extention of this article is published SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table

Reference : Pinal Dave (http://www.SQLAuthority.com)