Tuesday 27 March 2007

Paging with MS SQL Server 2005

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

2 comments:

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03