批 sqlserver 2000 三种分页方案

先说最流行的那种

第一种方案:
两次top分页,原型如下:  

SELECT   *   FROM   (
SELECT   TOP   页面容量   *   FROM   (

SELECT   TOP   页面容量*当前页码   *   FROM  

表   WHERE   条件   ORDER   BY   字段A   ASC

)   AS   TEMPTABLE1   ORDER   BY   字段A   DESC

)   AS   TEMPTABLE2   ORDER   BY   字段A   ASC

弊病:1   强制排序   否则不能分页,虽然目前基本上查询表都要排序

2.   排序字段不能有空值即null,否则分页结果不符实际情况

3.   多次order   by   速度会快吗,有待我进一步大数据量测试

基于上面的分页原理,我写了一个存储过程,有兴趣的看看,如下;
-----------------------------------------------------------------------------------------------------------
alter   proc   sp_pagination
@tbName   varchar(200),   --表名,可多表,逗号分隔
@tbFields   varchar(500)= '* ',--字段名,如果多表请带前缀
@whereStr   varchar(300)= ' ',   --where子句,可为空,不带where
@orderStr   varchar(300),   --排序字段,可多个,要带desc或asc,不带order   by,必须,不能为空
--排序字段不能有空值,或者在where中排除空值或者用isnull函数解决

@needCound   bit   =   0,   --是否需要得到纪录总数
@pageIndex   int   =0,   --页索引
@pageSize   int=10,   --页大小
@recordCount   BIGINT   =0   output,   --返回纪录总数
@pageCount   int   =0   output   --返回页总数

as
declare   @sql   nvarchar(1300)   --主sql语句
declare   @orderStr2   varchar(300)   --order   by子句
set   @orderStr   =   LOWER(@orderStr)
set   @orderStr2   =   REPLACE(@orderStr, '   desc ', '   @a@ ')
set   @orderStr2   =   REPLACE(@orderStr2, '   asc ', '   @d@ ')
set   @orderStr2   =   REPLACE(@orderStr2, '   @a@ ', '   asc ')
set   @orderStr2   =   REPLACE(@orderStr2, '   @d@ ', '   desc ')
set   @orderStr   =   '   order   by   '   +   @orderStr
set   @orderStr2   =   '   order   by   '   +   @orderStr2

if(@whereStr   is   not   null   and   @whereStr   != ' '   )
set   @whereStr   =   '   where   '   +   @whereStr
else
set   @whereStr   =   ' '

if(@needCound   !=   0   or   @pageIndex   =   0)   --以下获得纪录总数
begin
DECLARE   @R   BIGINT
SET   @sql=   N 'select   @R=count(*)   from   '+@tbName
EXEC   SP_EXECUTESQL   @SQL,N '@R   BIGINT   OUTPUT ',@R   OUTPUT
SET   @recordCount   =   @R
set   @pageCount   =   ((@recordCount-1)/@pageSize)+1  
end

if(@pageIndex <2)   --如果是第一页
begin
set   @pageIndex   =   1
set   @sql= 'select   top   '+   str(@pageSize)   +   '   '+   @tbFields   +   '   from   '   +   @tbName   +   @whereStr   +   @orderStr;
end

else   --其它页
begin
SET   @sql=   'SELECT   '   +   @tbFields   +   '   FROM   ( '
+   'SELECT   TOP   '   +   STR(@pageSize)   +   '   '+   @tbFields   +   '   FROM   ( '
+   'select   top   '   +   STR(@pageSize*@pageIndex)   +   '   '+   @tbFields   +   '   FROM   '
+   @tbName   +   @whereStr   +   @orderStr   +   ')   as   a '
+   @orderStr2   +   ')   as   b '   +   @orderStr
end
print   @sql  
EXEC   SP_EXECUTESQL   @sql  

--测试
declare   @a   BIGINT,@b   BIGINT
exec   sp_pagination   'orders ', '* ', ' ', 'orderid   asc ',1,5,10,@a   output,@b   output
print   @a
print   @b
------------------------------------------------------------------------------------------------------

第二种方案:基于not   in   ,原型如下

select   top   页大小   *
from   testtable
where   (
id   not   in
(select   top   页大小*页数   id   from   表   order   by   id)
)
order   by   id

弊病:1   强制排序
2   排序列必须是唯一列,否则分页情况不符实际
3.   使用not   in,速度慢,

第三种方案:   基于max   或者   min   ,原型如下:

select   top   页大小   *
from   testtable
where   (
id   >   (  
select   max(id   )   from   (   select   top   页大小*页数   id   from   表
order   by   id   )   as   t
)
)  
order   by   id

弊病:1   强制排序
2   排序列必须是唯一列,否则分页情况不符实际

最后总结:
sqlserver   分页就是烂,第二第三种方案基本上是淘汰掉了
,因为现在基本上什么表都是根据添加时间来排序,所以那两种方案
没有用,真亏作者也敢发布出来,
只有第一种方案还是稍微能用一下,但还是要复杂的拼sql   语句,不方便,要通用于所有表有点难度,
象oracle   就很方便了,基于rownum,传入一个sql   查询语句,这个查询语句爱怎么写就怎么写,反正保证它得到一个结果集就行,不像sqlserver又是要求唯一健又是要求必须排序,把一个结果集颠来倒去,不慢才怪呢,

题外话:
sqlserver   2005已经支持行号了,就象oracle那样,改天我再测试一下是否方便.
另外,网上流传的很多分页存储过程,很多都是只适合单表查询或者
单个字段排序,   现实中很少单表查询和单字段排序的,基本上也是没什么用,个别适合多表查询的,存储过程里面又写得复杂得半死,分页效率用屁股想想就知道了