database level paging in Tsql 2005 with common table expression

Share

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           ,

OrderID ,

OrderDate,

CustomerID,

EmployeeID

FROM dbo.Orders )

SELECT *    FROM OrdersRN  WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize  ORDER BY OrderDate,

OrderID;