简析基于SQL SERVER分页存储过程的演进

---

针对数据库数据在UI界面上的分页是老生常谈的问题了，网上很容易找到各种“通用存储过程”代码，而且有些还定制查询条件，看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQL SERVER 2000的分页存储过程，同时谈谈SQL SERVER 2005下分页存储过程的演进。

　　在进行基于UI显示的数据分页时，常见的数据提取方式主要有两种。种是从数据库提取所有数据然后在系统应用程序层进行数据分页，显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。

　　以下是笔者对两种实现方式所做的优缺点比较，针对应用程序编写，笔者以.NET技术平台为例。

　　在编写具体SQL语句之前，定义以下数据表。

　　数据表名称为：Production.Product。Production为SQL SERVER 2005中改进后的数据表架构，对举例不造成影响。

　　包含的字段为：

　　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'
END
ELSE
BEGIN
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
END
ELSE
BEGIN
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 语言对分页功能的支持进步不少。使分页实现趋向于简单化。