Paging Records Using SQL Server 2005 Database - ROW

本文介绍如何使用SQL Server 2005的ROW_NUMBER()函数进行数据库记录分页，通过示例展示了如何获取指定数量的记录，以及如何通过存储过程实现更高效的分页操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Introduce how to Paging Records using Sql server 2005 's Row_Number() Function.

[@more@]

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.

Source: David Hayden ( Sarasota Web Design )

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10771986/viewspace-968265/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10771986/viewspace-968265/