Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications.  ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.

If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows: 

SELECT  Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row
>= 1 AND Row <= 10 

The second page of 10 records would then be as follows: 

SELECT  Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row
>= 11 AND Row <= 20 

If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20: 

SELECT  Description, Date
FROM (SELECT TOP
20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row
>= 11 AND Row <= 20 

We can rap this up in a Stored Procedure as follows: 

CREATE PROCEDURE dbo.ShowLog
@PageIndex INT,
@PageSize INT
AS

BEGIN

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

SELECT Date, Description
FROM LogEntries
WHERE Row between

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END  

It is only available in SQL Server 2005, but it is a heck of a lot easier and more intuitive than creating temp tables and using other stored procedures that I have used in the past.  However, if you want to target your application for SQL Server 2000 use, I would stick with a record paging solution that works for both SQL Server 2005 and SQL Server 2000 Databases.

Ebook Download
View all
Learn
View all