How does paging work with ROW_NUMBER in SQL Server?
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