How to do pagination in SQL Server 2008

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 CTEResults
WHERE RowNum BETWEEN 10 AND 20;

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)

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.

1) CREATE DUMMY DATA

CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))

DECLARE @id INT = 1

WHILE @id < 200

BEGIN
INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END

2) NOW APPLY THE SOLUTION.

This case assumes EMPID to be unique and sorted column.

Off-course, you will apply it a different column...

DECLARE @pageSize INT = 20

SELECT * FROM (

SELECT *, PageNumber =  CEILING(CAST(EMPID AS FLOAT)/@pageSize)   
FROM #employee
) MyQuery

WHERE MyQuery.PageNumber = 1