KARPACH

WEB DEVELOPER BLOG

T-SQL: How to do SQL Server paging with ROW_NUMBER()?

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
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
      AND @PageNum * @PageSize
ORDER BY OrderDate,OrderID;
Posted on November 22, 2007 by