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.

Answer 3

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

Answer 4

I don't think it evaluates to return all rows in the original table. SQL server optimizes. Otherwise it will take a huge amount of time to select a million entries. I am currently using this and it is much faster than selecting all rows. So, sure does not get all the rows. It is however slower than just fetching the first five rows, probably due to time taken in ordering

Answer 5

DECLARE @PageIndex int;
DECLARE @PageSize int;
SET @PageIndex = 4;
SET @PageSize = 5;
;With ranked AS   --- Or you can make it a view
(
   SELECT ROW_NUMBER() OVER(ORDER BY IdentityId) AS RowNum,  *
   FROM logeventnew
)
SELECT *   --Your fields here
FROM Ranked
WHERE RowNum BETWEEN ((@PageIndex - 1) * @PageSize + 1)
    AND (@PageIndex * @PageSize)
ORDER BY IdentityId