How does paging work with ROW_NUMBER in SQL Server?

Question

I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee selects all records in the Employee table (to create the MyRowNumber values).

I think, this causes selection of all records in the Employee table.

Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee table too?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize 

Answer 1

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 ...; 

Answer 2

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.