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;

Advertisements

About bpostaci
Escalation Engineer in Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: