Paging can be easily achieved in MS SQL Server 2005 by using the ROW_NUMBER function and CTE (Common Table Expressions). The ROW_NUMBER function provides the ability to issue a query and just return a subset of the result set and the CTE allows us to define the temporary result set with clause. The below example shows how to retrieve first ten Patients from a result set using the CTE and ROW_NUMBER function.
USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS ‘RowNumber’
FROM Patient
)
SELECT * FROM
PatientDetail
WHERE
RowNumber between 1 and 10;
GO
The OVER clause is used to determine the partitioning and ordering of the intermediary result set before the ROW_NUMBER function is applied. The SELECT statement can be parameterized to retrieve data for the specified Range as given below.
USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS ‘RowNumber’
FROM Patient
)
SELECT * FROM
PatientDetail
WHERE RowNumber between
@RowNumberFrom and @RowNumberTo;
GO
