how to do pagination in sql server 2008

Question

How do you do pagination in sql server 2008 ?

Answer 1

You can use ROW_NUMBER() e.g.

WITH CTEResults AS
(
    SELECT IDColumn, SomeField, DateField ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
    FROM MyTable
)

SELECT * 
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;

Answer 2

You can try something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

DECLARE @PageSize INT,
        @Page INT

SELECT  @PageSize = 10,
        @Page = 2

;WITH PageNumbers AS(
        SELECT Val,
                ROW_NUMBER() OVER(ORDER BY Val) ID
        FROM    @Table
)
SELECT  *
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)

Answer 3

Just like in SQL Server 2005.

Answer 4

These two articles contain the information which I found useful some time ago:

http://www.asp101.com/articles/gal/effectivepaging/default.asp, http://aspnet.4guysfromrolla.com/articles/031506-1.aspx