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

作者:郑佐
日期:2006-9-30


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

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zhzuo/archive/2006/09/30/1313274.aspx

 

 

 

SQL分页语句

有关分页 SQL 的资料很多,有的使用存储过程,有的使用游标。本人不喜欢使用游标,我觉得它耗资、效率低;使用存储过程是个不错的选择,因为存储过程是经过预编译的,执行效率高,也更灵活。先看看单条 SQL 语句的分页 SQL 吧。

方法1:
适用于 SQL Server 2000/2005
SELECT   TOP  页大小  *
FROM  table1
WHERE  id  NOT   IN
          (
          
SELECT   TOP  页大小 * ( - 1 ) id  FROM  table1  ORDER   BY  id
          )
ORDER   BY  id

方法2:
适用于 SQL Server 2000/2005
SELECT   TOP  页大小  *
FROM  table1
WHERE  id  >
          (
          
SELECT   ISNULL ( MAX (id), 0
          FROM  
                (
               
SELECT   TOP  页大小 * ( - 1 ) id  FROM  table1  ORDER   BY  id
                ) 
A
          )
ORDER   BY  id

方法3:
适用于 SQL Server 2005

SELECT   TOP  页大小  *  
FROM  
        (
        
SELECT  ROW_NUMBER()  OVER  ( ORDER   BY  id)  AS  RowNumber, *   FROM  table1
        ) A
WHERE  RowNumber  >  页大小 * (页数 - 1 )


说明,页大小:每页的行数;页数:第几页。使用时,请把“页大小”和“页大小*(页数-1)”替换成数字。

 

 

 

其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。

通过SQL 查询分析器,显示比较:我的结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页)   效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页)    效率最差,但是最为通用

分类: 数据库
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值