Best SQL Server Pagination Method

SQL Server 2012 actually employs a different method for pagination that is built in using the ORDER BY Clause. However for other versions, the most efficient pagination method is actually simple. Aren’t most things like this?

It involves using (the mighty powerful) ROWCOUNT statement — twice.

Anyways, the code:

CREATE PROCEDURE [dbo].[spUsersPaged]
(
    @RowBegin INT,
    @MaxRows INT
)
AS
BEGIN

    DECLARE @StartID INT   

    SET ROWCOUNT @RowBegin
    SELECT @StartID = UserID
    FROM dbo.Users u
    ORDER BY UserID

    -- Set maximum number of rows to return
    SET ROWCOUNT @MaxRows

    SELECT *
    FROM dbo.Users u
    WHERE UserID >= @StartID
    ORDER BY u.UserID

    SET ROWCOUNT 0

END

That’s it for the first pagination sample. You may try other methods, however I challenge all Ye to overcome thy double ROWCOUNT method. Comment below please.

To give a variety, I’m sharing another pagination method. This one seems more elegant. It uses a Common Table Expression) CTE, and the RowNumber function.

SET @rowsPerPage = 10
SET @pageNum = 3  
 
WITH SQLPaging
AS
(
SELECT TOP(@rowsPerPage * @pageNum)
ResultNum = ROW_NUMBER() OVER (ORDER BY id)
,id
FROM dbo.Table1
)
SELECT *
FROM SQLPaging
WHERE ResultNum > ((@pageNum - 1) * @rowsPerPage)

Yet is it elegant, with speed?? ..uh not so much.

4 comments
Varun Aggarwal 06 Jun 2013 at 12:45 pm

select * from address tab1,(select id,ROW_NUMBER() OVER (ORDER BY id) rownumber from address) tab2 where tab1.id=tab2.id and tab2.rownumber>3 and tab2.rownumber<7

just thaught at random. please let me know about its performance.

Anuj Rathi 24 Feb 2013 at 6:33 am

yes, this is very helpful. Can I use the below code also:

SET @rowsPerPage = 10
SET @pageNum = 3

SELECT * FROM
AS
(
SELECT TOP(@rowsPerPage * @pageNum)
ResultNum = ROW_NUMBER() OVER (ORDER BY id), id
FROM dbo.Table1
) A
WHERE A.ResultNum > ((@pageNum – 1) * @rowsPerPage)

Rahul Verma 05 Jan 2012 at 12:04 pm

Thanks .It is very helpful for me. I want to paging on a condition that I pass the table name dynamically and result is Generated

John 04 Jan 2012 at 1:10 am

Excelent method. Thanks.

Featured Articles

 Site Author