SqlServer2005分页方案，很实用，很快！

1、为什么要使用row方案：
在oracle里有row_number虚列，
mySql有limit关键字分页，
他们都有一个比较通用的分页方案，
使得hibernate等类似的程序可以拼接sql字符串提供通用的分页。
而sqlserver却没有这样的分页方案。
于是乎，本人稍稍改装row_number()over(order by )用法，获得了一个通用的分页方案。
如提供了sql如下：

 

select * from Student where Age>18 order by Age

被row方案的分页程序处理后变成
（在select 后面添加 top 开始位置 0 __tc，在外层嵌套固定模式的查询sql）

select *from (    select row_number()over(order by __tc__)__rn__,*    from (select top 开始位置+10 0 __tc__,* from Student where Age>18 order by Age)t)ttwhere __rn__>开始位置

这样就得到了拼接出通用的分页sql方案了。
并且经过本人测试发现，这套方案的运行速度不逊于任何一套其他方案。
其余各方面效率还有待考察，忘高人指点了。

2、row方案的排序：
row方案可以任意排序，
只要修改最内层的select排序即可，
应该来说是很简单易用的。

3、row方案和普通row_number()方案的区别：
一般的row方案：

select *from (select top 开始位置+10 row_number()over(order by Id)__rn__, * from Student)twhere __rn__>=开始的位置

使用了over(order by 表中的列)，照成了必须由用户提供这个列，
而不容易使用分页程序生成分页sql（如hibernate分页）。
而row方案使用的是一个常数列tempColumn，值永远是0。

 

select *from (  select row_number()over(order by TempColmun) *  from (    select top 开始的位置 0 as TempColmun,*    from Student order by Id  )tt)twhere rowNumber >=开始的位置

这个列是静态的，只是为了使用row_number（）函数，
并不是真正的order by 依据，order by 实际看最内层。

我分析是因为row方案使用一个静态的列tempColumn，
这样可能被sql分析程序认为是无需排序的，省下了排序过程的开销。

4、数据测试：
现只在我一台机子上试过，
希望路过的各位随手帮忙测试一下。
这也是我迟迟不结贴的缘故。
举手之劳，复制sql运行即可：

--插入测试数据200w条，可能会很久create table Student(  Id int PRIMARY KEY identity(1,1),  Name nvarchar(50),  Age int)insert Student(Name,Age)values('Name',18)while (select count(*) from Student)<2000000  insert Student select Name,Age from Student

运行测试代码：

--开始测试查询declare @now datetime--max方案select 'max'方案select @now=getdate()--beginselect top 10 * from Studentwhere Id>(  select max(Id)  from (    select top 1999990 Id from Student order by Id)tt)--enddeclare @maxDiff intselect @maxDiff=datediff(ms,@now,getdate())--top方案select 'top'方案select @now=getdate()--beginselect top 10 * from Studentwhere Id not in(select top  1999990 Id from Student)--enddeclare @topDiff intselect @topDiff=datediff(ms,@now,getdate())--row方案select 'row'方案select @now=getdate()--beginselect *from (select row_number()over(order by tc)rn,*from (select top 2000000 0 tc,* from Student)t)ttwhere rn>1999990--enddeclare @rowDiff intselect @rowDiff=datediff(ms,@now,getdate())--row_number方案select 'row_number'方案select @now=getdate()--beginselect *from(select top 2000000 row_number()over(order by Id)rn,* from Student)twhere rn>1999990--enddeclare @row_numberDiff intselect @row_numberDiff=datediff(ms,@now,getdate())--记录结果select '第20万页'页码,@maxDiff max方案,@topDiff top方案,@rowDiff row方案,@row_numberDiff row_number方案

这套方案（下面简称row方案）是本人借鉴Oracle的row_number分页方法和sqlServerrow_number结合+上top分页方案合体版，经过本人初步测试。
效率非常快。（本人测试非常业余，还望高人帮忙测试。）
row方案的具体操作方法在这章帖子里：

一套原创的sqlserver通用分页方案 忘高人测试效率 先阿里嘎多了


比较了3种分页方式，分别是max方案，top方案，row方案

效率：
  第1：row
  第2：max
  第3：top

缺点：
  max：必须用户编写复杂Sql，不支持非唯一列排序
  top：必须用户编写复杂Sql,不支持复合主键
  row：不支持sqlServer2000

测试数据：
共320万条数据，每页显示10条数据，分别测试了2万页、15万页和32万页。

页码，top方案，max方案，row方案
2万，60ms，46ms，33ms
15万，453ms，343ms，310ms
32万，953ms，720ms，686ms


具体操作sql代码如下：

top方案：

select top 10 * from Table1where Id not in(select top 开始的位置 Id from Table1)

max:

select top 10 * from Table1where Id>(select max(Id)from (select top 开始位置 Id from Table1order by Id)tt)

row:

select *from (    select row_number()over(order by tempColumn)tempRowNumber,*    from (select top 开始位置+10 tempColumn=0,* from Table1)t)ttwhere tempRowNumber>开始位置