How to do pagination in SQL Server 2008

You can try something like

DECLARE @Table TABLE(        Val VARCHAR(50))DECLARE @PageSize INT,        @Page INTSELECT  @PageSize = 10,        @Page = 2;WITH PageNumbers AS(        SELECT Val,                ROW_NUMBER() OVER(ORDER BY Val) ID        FROM    @Table)SELECT  *FROM    PageNumbersWHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)        AND (@Page * @PageSize)

You can use 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.

Example:

WITH CTEResults AS(    SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum    FROM MyTable)SELECT * FROM CTEResultsWHERE RowNum BETWEEN 10 AND 20;

SQL Server 2012 provides pagination functionality (see http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server)

In SQL2008 you can do it this way:

declare @rowsPerPage as bigint; declare @pageNum as bigint; set @rowsPerPage=25; set @pageNum=10;   With SQLPaging As   (     Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc)     as resultNum, *     FROM Employee )select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)

Prooven! It works and scales consistently.