Best SQL Server Pagination Method

21 December 2009, 11:28 pm

The best pagination method is actually mind-bogglingly simple. Aren’t all 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. Well, aren’t we excited? 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.

Share and Enjoy: