Posted in General

Paging in SQL Server 2005

Oracle has ROWNUM column which will let us select paged records(gets you 21-30 records) by using the following query.
SELECT * FROM (SELECT RowNum, P.* FROM Products P) As T WHERE T.RowNum BETWEEN 21 and 30

SQL Server 2000 didn’t have this feature. Paging in SQL Server 2000, was a pain, we had to build dynamic SQL to do the paging or use temp tables.

SQL Server 2005 has “RowNum” now

Its better to be late than never. This following query will give us the records from 21-30. Both 21 and 30 can be replaced by @Start and @End variables.

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Title DESC) AS RowNum, *
FROM Product) As P
WHERE RowNum BETWEEN 21 AND 30

Also SQL Server 2005’s TOP keyword takes variables now. I was used to a solution similar to a comment at http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx#1524. Now we can do this instead of dynamic SQL.

SELECT * FROM
(SELECT TOP @PageSize ROW_NUMBER() OVER (ORDER BY Title DESC) AS RowNum, *
FROM Product) P
WHERE P.RowNum> @PageSize * (@PageNumber-1)
You still need ROW_NUMBER, unless you sort other way round.

Advertisements

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