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.
