T-SQL Row_Number() and Paging

In MS SQL 2005 you can use Row_Number() function for paging:

DECLARE @PageNumber AS INT;
DECLARE @PageSize AS INT;
SET @PageNumber = 2;
SET @PageSize = 10;

WITH CustomerCTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY LoginDate, CustomerID) AS RowNumber
          ,CustomerID
          ,LoginDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Customers
)

SELECT *
  FROM CustomerCTE
 WHERE RowNumber BETWEEN (@PageNumber – 1) * @PageSize + 1 AND @PageNumber * @PageSize
 ORDER BY LoginDate , CustomerID;