In MSSQL 2000 we used to do paging either by dynamic sql or by some
advanced techniques like the example with rowcount.
In MSSQL 2005 with the introduction of ROW_NUMBER
function life is a lot easier.
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum ,
FROM dbo.Orders )
SELECT * FROM OrdersRN WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ORDER BY OrderDate,
Leave a Comment
Notify me of follow-up comments by email.
Notify me of new posts by email.
Next post: how to upload larger files in asp.net 2
Previous post: Common Table Expression
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer or other users. All products mentioned in this website are trademarks of their respective owners.