Ad-Hoc Query Paging in SQL Server 2012

One thing applications tend to do when retrieving a lot of data from SQL Server is to page the results.

Since SQL Server 2005, I tended to do this using the ROW_NUMBER() function. Like this

SELECT *

FROM   (SELECT p.firstname,

               p.lastname,

               Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

        FROM   person.person p) AS person

WHERE  person.rownum BETWEEN 11 AND 20

or like this

WITH person(firstname, lastname, rownum)

     AS (SELECT p.firstname,

                p.lastname,

                Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

         FROM   person.person p)

SELECT *

FROM   person p

WHERE  p.rownum BETWEEN 11 AND 20

I would of course parameterize 11 and 20 and call that in a stored procedure or parameterized SQL statement. Linq to Sql or the Entity Framework currently generates a similar SQL statement.

OFFSET ... FETCH NEXT 

Now in SQL Server 2012 RC0 we can achieve the same thing using the offset  ... fetch next clause.

The Order By statement now has an additional clause we can use as follows:

SELECT p.firstname,

       p.lastname

FROM   [Person].[Person] p

ORDER  BY p.firstname DESC


OFFSET 10 rows

FETCH NEXT 10 rows only

The offset clause specifies the number to rows to skip before returning the rows from the query expression.

The fetch specifies the number of rows to return after the OFFSET clause has been processed.

In terms of performance, I have not done any extensive performance tests but I do not believe there is a significant performance difference between the two.

The query execution plan for the two statements are similar.

James