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.