Best SQL Server Pagination Method
-
Posted on December 21, 2009 by Derek Dieter
-
0
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:
(
@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 @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.
- Comments (RSS)
- Trackback
- Permalink
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)
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
Excelent method. Thanks.
