How does SQL server paging work with ROW_NUMBER?

444 Asked by AngelaBaker in SQL Server , Asked on Jul 12, 2021

I have an Employee table that has one million records. I have the 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 the 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? How does SQL Server Paging work?

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

Answered by Anna Ball

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

How does SQL server paging work with ROW_NUMBER?

Your Answer