How does SQL server paging work with ROW_NUMBER?

In SQL, pagination refers to reducing the number of records from structured query language (SEQUEL or SQL) query result sets. For instance, most database platforms implement SQL result set pagination by using the OFFSET/FETCH and LIMIT clauses.

An alternative to test might be:

    ;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp) SELECT e.columns FROM x INNER JOIN dbo.Emp AS e ON x.EmpID = e.EmpID WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

    ;WITH x AS ( SELECT EmpID FROM dbo.Emp ORDER BY EmpID OFFSET @PageSize * (@Index - 1) ROWS FETCH NEXT @PageSize ROWS ONLY ) SELECT e.columns FROM x INNER JOIN dbo.Emp AS e ON x.EmpID = e.EmpID ORDER BY ...;

I also blogged about this in more detail here:

SQL Server v.Next (Denali) : Using the OFFSET clause (paging)
Pagination with OFFSET / FETCH : A better way