ali raza

C#, .Net ,Asp.net and my thoughts

Category Archives: Sql Server 2005

database level paging in Tsql 2005 with common table expression

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;