SQL Server 2011 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.

• sql server pagination
• sql server 2008 pagination
• sql pagination
• pagination sql server
• pagination in sql server 2008