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

Although you may not know the mechanism behind it, you could test this yourself by comparing the performance of your query to: select * from Employee.

The more recent versions of SQL Server do a pretty good job of optimizing, but it can depend on several factors.

How your ROW_NUMBER function performs is going to be driven by the Order By clause. In your example, most would guess EmpID is the primary key.

There are some where clauses that are so complex and/or poorly coded or indexed, you may be better off just returning the whole dataset (it's rare and can be fixed). Using BETWEEN has issues.

Before you assume it would be better to return all the rows to your application and let it figure it out, you should work on optimizing your query. Check the estimates. Ask the Query Analyzer. Test some alternatives.

I know the question is regarding row_number() but i want to add one new feature of sql server 2012. In sql server 2012 new feature OFFSET Fetch next introduced and it is very fast than row_number(). I have used it and it gives me good result hope you guys also fill same experience.

I found one example on http://blogfornet.com/2013/06/sql-server-2012-offset-use/

which is useful. Hope it will help you too for implement new features....