MSSQL教程之复杂谈基于SQL SERVER 分页存储历程的演...
在ORDERBY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。（虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快ORDERBY方面也没什么作用）。server|存储历程|分页复杂谈基于SQLSERVER分页存储历程的演进<P>郑佐
日期：2006-9-30
针对数据库数据在UI界面上的分页是陈词滥调的成绩了，网上很简单找到各类“通用存储历程”代码，并且有些还定制查询前提，看上往利用很便利。笔者盘算经由过程本文也来复杂谈一下基于SQLSERVER2000的分页存储历程，同时谈谈SQLSERVER2005下分页存储历程的演进。
在举行基于UI显现的数据分页时，罕见的数据提取体例次要有两种。第一种是从数据库提取一切数据然后在体系使用程序层举行数据分页，显现以后页数据。第二种分页体例为从数据库掏出必要显现的一页数据显现在UI界面上。以下是笔者对两种完成体例所做的优弱点对照，针对使用程序编写，笔者以.NET手艺平台为例。种别SQL语句代码编写计划时功能第一种语句复杂，兼容性好很少完整撑持数据越年夜功能越差第二种看详细情形较多部分撑持优秀，跟SQL语句有关
关于第一种情形本文不盘算举例，第二种完成体例笔者只以两次TOP体例来举行会商。
在编写详细SQL语句之前，界说以下数据表。
数据表称号为：Production.Product。Production为SQLSERVER2005中改善后的数据表架构，对举例不形成影响。
包括的字段为：列名数据范例同意空申明ProductIDInt产物ID，PK。NameNvarchar(50)产物称号。
不难发明以上表布局来自SQLSERVER2005样例数据库AdventureWorks的Production.Product表，而且只取个中两个字段。分页相干元素：
PageIndexC页面索引计数，计数0为第一页。
PageSizeC每一个页面显现巨细。
RecordCountC总纪录数。
PageCountC页数。
关于后两个参数，笔者在存储过程当中以输入参数供应。
1．SQLSERVER2000中的TOP分页
CREATEPROCEDURE
@PageIndexINT,/*@PageIndex从计数,0为第一页*/
@PageSizeINT,/*页面巨细*/
@RecordCountINTOUT,/*总纪录数*/
@PageCountINTOUT/*页数*/AS/*猎取纪录数*/
SELECT@RecordCount=COUNT(*)FROMProduction.Product
/*盘算页面数据*/
SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)
/*TOP纪录数*/
DECLARE@TOPCOUNTINT
SET@TOPCOUNT=@RecordCount-@PageSize*@PageIndex
DECLARE@SQLSTRNVARCHAR(1000)
IF@PageIndex=0OR@PageCount<=1
BEGIN
SET@SQLSTR=NSELECTTOP+STR(@PageSize)+
ProductID,NameFROMProduction.ProductORDERBYProductIDDESC
END
ELSE
BEGIN
IF@PageIndex=@PageCount-1
BEGIN
SET@SQLSTR=NSELECT*FROM(SELECTTOP+STR(@TOPCOUNT)+
ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESC
END
ELSE
BEGIN
SET@SQLSTR=NSELECTTOP+STR(@PageSize)+*FROM(SELECTTOP+STR(@TOPCOUNT)+
ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESC
END
END/*实行*/
EXEC(@SQLSTR)
以上存储历程对页数举行判别，假如是第一页或最初一页，举行特别处置。其他情形利用2次TOP翻转。个中排序前提为ProductID倒序。最初经由过程EXECUTE实行SQL字符串拼串。
2．SQLSERVER2005中的TOP分页
CREATEPROCEDURE.
@PageIndexINT,
@PageSizeINT,
@RecordCountINTOUT,
@PageCountINTOUT
AS/*猎取纪录数*/
SELECT@RecordCount=COUNT(*)FROMProduction.Product
/*盘算页面数据*/
SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)
/*TOP纪录数*/
DECLARE@TOPCOUNTINT
SET@TOPCOUNT=@RecordCount-@PageSize*@PageIndex
/*基于SQLSERVER2005*/
IF@PageIndex=0OR@PageCount<=1
BEGIN
SELECTTOP(@PageSize)ProductID,NameFROMProduction.ProductORDERBYProductIDDESC
END
ELSE
BEGIN
IF@PageIndex=@PageCount-1
BEGIN
SELECT*FROM(SELECTTOP(@TOPCOUNT)ProductID,NameFROMProduction.ProductORDERBYProductIDASC)T
ORDERBYProductIDDESC
END
ELSE
BEGIN
SELECTTOP(@PageSize)*FROM(SELECTTOP(@TOPCOUNT)ProductID,NameFROMProduction.ProductORDERBYProductIDASC)T
ORDERBYProductIDDESC
END
END以上存储历程是利用2005的TOP(表达式)新功效，制止了字符串拼串，使布局化查询言语变得简便。完成的为一样的功效。
3．SQLSERVER2005中的新分页
CREATEPROCEDURE.
@PageIndexINT,
@PageSizeINT,
@RecordCountINTOUT,
@PageCountINTOUT
AS/*猎取纪录数*/
SELECT@RecordCount=COUNT(*)FROMProduction.Product
/*盘算页面数据*/
SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)
/*基于SQLSERVER2005*/
SELECTSerialNumber,ProductID,NameFROM
(SELECTProductID,Name,ROW_NUMBER()OVER(ORDERBYProductIDDESC)ASSerialNumberFROMProduction.Product)AST
WHERET.SerialNumber>(@PageIndex*@PageSize)andT.SerialNumber<=((@PageIndex+1)*@PageSize)
第三个存储历程利用2005下新的功效，完成的分页存储历程功效加倍复杂了然，并且加倍简单了解。注重这里的ProductID为主键,依据ProductID举行排序天生ROW_NUMBER，经由过程ROW_NUMBER来断定详细的页数。
经由过程对三个分页存储历程的对照，可见SQLSERVER的TSQL言语对分页功效的撑持前进很多。使分页完成趋势于复杂化。其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。 多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。 你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。 一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。） 另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程); 一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。） 这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？ 其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！
页:
[1]