TSQL : Paging with ROW_NUMBER()

In MSSQL 2000 we used to do paging either by dynamic sql or by some advanced techniques,In MSSQL 2005 with the introduction of ROW_NUMBER function life is a lot easier.
You can use ROW_NUMBER to make light work of paging , such as front-end web applications that need to retrieve ten records at a time.
The next query returns ten names from the Contact table beginning at the specified row number.
DECLARE @start INT;
SELECT @start = 10;
WITH PageContacts AS
(
SELECT ROW_NUMBER() OVER
(
ORDER BY LastName,
FirstName,
MiddleName
)
AS PosNo, FirstName, MiddleName, LastName
FROM Contact
)
SELECT PosNo, FirstName, MiddleName, LastName
FROM PageContacts
WHERE PosNo BETWEEN @start AND @start + 10;

The script begins by defining the start position as the ten :

DECLARE @start INT;
SELECT @start = 10;

To use the ROW_NUMBER function to limit the result set to the ten rows beginning at the start position, the sample uses a CTE to wrap the ROW_NUMBER–generating query:

WITH PageContacts AS
(
SELECT ROW_NUMBER() OVER
(
ORDER BY LastName,
FirstName,
MiddleName
)
AS PosNo, FirstName, MiddleName, LastName
FROM Contact
)

SQL, and consequently T-SQL, has absolutely no concept of guaranteed row order without an ORDER BY clause. The OVER keyword provides the mandatory ORDER BY clause to guarantee this proper row numbering order. The final step is to select the columns from the CTE and use the BETWEEN operator to limit the result set to ten rows:

SELECT PosNo,
FirstName,
MiddleName,
LastName
FROM PageContacts
WHERE PosNo BETWEEN @start AND @start + 9;

By changing the @start you can get another page

Best regards