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:

[cc lang=”sql”]
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
[/cc]

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.

[cc lang=”sql”]
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)
[/cc]

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