| 订阅 | 在线投稿
分享
 
 
 

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

来源:互联网网民  宽屏版  评论
2006-12-10 06:53:51

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

 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
简单谈基于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 语言对分页功能的支持进步不少。使分页实现趋向于简单化。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有