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

转载 2007年02月27日 16:52:00
 作者:郑佐
日期:2006-9-30
针对数据库数据在UI界面上的分页是老生常谈的问题了,网上很容易找到各种“通用存储过程”代码,而且有些还定制查询条件,看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQL SERVER 2000的分页存储过程,同时谈谈SQL SERVER 2005下分页存储过程的演进。
在进行基于UI显示的数据分页时,常见的数据提取方式主要有两种。第一种是从数据库提取所有数据然后在系统应用程序层进行数据分页,显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。
以下是笔者对两种实现方式所做的优缺点比较,针对应用程序编写,笔者以.NET技术平台为例。
类别
SQL语句
代码编写
设计时
性能
第一种
语句简单,兼容性好
很少
完全支持
数据越大性能越差
第二种
看具体情况
较多
部分支持
良好,跟SQL语句有关
 
对于第一种情况本文不打算举例,第二种实现方式笔者只以两次TOP方式来进行讨论。
在编写具体SQL语句之前,定义以下数据表。
数据表名称为:
Production.ProductProductionSQL 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)

以上存储过程对页数进行判断,如果是第一页或最后一页,进行特殊处理。其他情况使用2TOP翻转。其中排序条件为ProductID倒序。最后通过EXECUTE执行SQL字符串拼串。
2SQL 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 
以上存储过程是使用2005TOP (表达式) 新功能,避免了字符串拼串,使结构化查询语言变得简洁。实现的为同样的功能。
3SQL 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 语言对分页功能的支持进步不少。使分页实现趋向于简单化。 

高效的SQLSERVER分页查询(推荐)

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询...
  • qiaqia609
  • qiaqia609
  • 2014年11月24日 14:53
  • 152787

给初学者分享四种 Sql Server分页查询使用示例

 当我们显示列表信息的时候,我们常常以分页形式显示,当然在ASP.NET中的ListView和GridView控件等都自带了分页功能,但是效率很低,无法适用大量数据,并且灵活性不高。因而我们一般...
  • taotoxht
  • taotoxht
  • 2015年07月27日 16:09
  • 971

高效的SQLSERVER分页查询(推荐)

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询...
  • JavaAlpha
  • JavaAlpha
  • 2013年11月22日 16:34
  • 3506

SqlServer2005分页方案,很实用,很快!

1、为什么要使用row方案: 在oracle里有row_number虚列, mySql有limit关键字分页, 他们都有一个比较通用的分页方案, 使得hibernate等类似的程序可以拼接sq...
  • johennes
  • johennes
  • 2013年06月21日 16:51
  • 857

《SQLServer》------常用的分页查询语句

前言 在SqlServer中,分页查询是经常用到的查询语句,一个好的分页查询语句,不能能将代码省略,还能提高运行效率,下面我们来探讨一下SQLServer中的分页查询语句。 具体的业务逻辑是这样的,我...
  • u013067402
  • u013067402
  • 2016年10月24日 12:01
  • 5767

SQL SERVER 2012 的分页方法创新版

关于SQL SERVER的分页方法,早在2004年,我就写了分页算法,
  • fengyun14
  • fengyun14
  • 2014年05月08日 21:11
  • 887

sql server 纯sql语句分页 存储过程 返回结果集及页总数等

经常要对数据库中的数据进行大量的查询,特别是业务后台,但是EF等提供的语句应用起来可能看起来复杂、臃肿,我们需要借助SQL语句、或者存储过程来取得结果 1、.net EF 定义参数 using (Y...
  • hejisan
  • hejisan
  • 2016年07月05日 23:49
  • 1657

SQLServer千万数量级分页公共存储过程

转自:http://blog.csdn.net/tem168/article/details/6427123 SQLServer千万数量级分页公共存储过程   通用的数据库分页存...
  • chelen_jak
  • chelen_jak
  • 2014年12月11日 21:24
  • 3107

MyBatis 分别结合 SQL Server、MySQL、ORACLE进行数据库分页

这里的实现相对于那些使用分页插件来说其实很简单,从页面上绑定得到第几页、每页数据条数,进行数据库分页实现。 这里使用到了bootstrap-table.js插件,可以结合中文插件bootstrap-...
  • Somhu
  • Somhu
  • 2017年12月13日 12:43
  • 62

sqlServer分页存储过程的调用

现象: sqlServer的分页一直相对比较复杂。这里使用存储过程实现分页逻辑解决办法 1:action获取查询的条件,初始化每页显示的大小,page代表当前查看第几页,默认设置为第一页。rows...
  • hcwbr123
  • hcwbr123
  • 2017年04月28日 11:39
  • 532
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:简单谈基于SQL SERVER 分页存储过程的演进
举报原因:
原因补充:

(最多只允许输入30个字)