基于SQL SERVER的分页存储过程

针对数据库数据在UI界面上的分页是老生常谈的问题了，网上很容易找到各种“通用存储过程”代码，而且有些还定制查询条件，看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQL SERVER 2000的分页存储过程，同时谈谈SQL SERVER 2005下分页存储过程的演进。 在进行基于UI显示的数据分页时，常见的数据提取方式主要有两种。第一种是从数据库提取所有数据然后在系统应用程序层进行数据分页，显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。 以下是笔者对两种实现方式所做的优缺点比较，针对应用程序编写，笔者以.NET技术平台为例。 类别 SQL 语句 代码编写 设计时 性能 第一种 语句简单，兼容性好 很少 完全支持 数据越大性能越差 第二种 看具体情况 较多 部分支持 良好，跟SQL语句有关  对于第一种情况本文不打算举例，第二种实现方式笔者只以两次TOP方式来进行讨论。在编写具体SQL语句之前，定义以下数据表。数据表名称为： Production.Product 。 Production 为 SQL SERVER 2005 中改进后的数据表架构，对举例不造成影响。包含的字段为： 列名 数据类型 允许空 说明 ProductID Int   产品ID，PK。 Name Nvarchar(50)   产品名称。 不难发现以上表结构来自SQL SERVER 2005 样例数据库AdventureWorks的Production. Product 表，并且只取其中两个字段 。  分页相关元素：PageIndex – 页面索引计数，计数0为第一页。PageSize – 每个页面显示大小 。 RecordCount – 总记录数 。 PageCount – 页数 。 对于后两个参数，笔者在存储过程中以输出参数提供。  1 ．SQL SERVER 2000中的TOP分页 CREATE PROCEDURE [Zhzuo_GetItemsPage]    @PageIndex INT, /*@PageIndex 从计数 ,0 为第一页 */     @PageSize  INT, /* 页面大小 */     @RecordCount INT OUT, /* 总记录数 */     @PageCount INT OUT /* 页数 */ AS /* 获取记录数 */ SELECT @RecordCount = COUNT(*) FROM Production.Product /* 计算页面数据 */ SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) /*TOP 记录数 */ DECLARE @TOPCOUNT INT SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex DECLARE @SQLSTR NVARCHAR(1000) IF @PageIndex = 0 OR @PageCount <= 1 BEGIN     SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+    'ProductID,Name FROM Production.Product ORDER BY ProductID DESC' ENDELSEBEGIN     IF @PageIndex = @PageCount - 1    BEGIN       SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +       'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'    END    ELSE    BEGIN       SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +       'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'    END END /* 执行 */ EXEC (@SQLSTR) 以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用 2 次 TOP 翻转。其中排序条件为 ProductID 倒序。最后通过 EXECUTE 执行 SQL 字符串拼串。 2 ． SQL SERVER 2005 中的 TOP 分页 CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]    @PageIndex INT,    @PageSize  INT,    @RecordCount INT OUT,    @PageCount INT OUT AS  /* 获取记录数 */ SELECT @RecordCount = COUNT(*) FROM Production.Product /* 计算页面数据 */ SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) /*TOP 记录数 */ DECLARE @TOPCOUNT INT SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex /* 基于 SQL SERVER 2005 */ IF @PageIndex = 0 OR @PageCount <= 1 BEGIN     SELECT TOP(@PageSize) ProductID,Name FROM Production.Product ORDER BY ProductID DESC ENDELSEBEGIN     IF @PageIndex = @PageCount - 1    BEGIN       SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T       ORDER BY ProductID DESC    END    ELSE    BEGIN       SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T       ORDER BY ProductID DESC    END END  以上存储过程是使用 2005 的 TOP ( 表达式 ) 新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。 3 ． SQL SERVER 2005 中的新分页 CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]    @PageIndex INT,    @PageSize  INT,    @RecordCount INT OUT,    @PageCount INT OUT AS  /* 获取记录数 */ SELECT @RecordCount = COUNT(*) FROM Production.Product /* 计算页面数据 */ SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) /* 基于 SQL SERVER 2005 */ SELECT SerialNumber,ProductID,Name FROM ( SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T WHERE T.SerialNumber > (@PageIndex * @PageSize)  and T.SerialNumber <= ((@PageIndex+1) * @PageSize)  第三个存储过程使用 2005 下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的 ProductID 为主键 , 根据 ProductID 进行排序生成 ROW_NUMBER ，通过 ROW_NUMBER 来确定具体的页数。 通过对三个分页存储过程的比较，可见 SQL SERVER 的 TSQL 语言对分页功能的支持进步不少。使分页实现趋向于简单化。