sql分页方式及效率比较

好久没有操作数据库分页了，在看了这篇文章（http://www.cnblogs.com/iamowen/archive/2011/11/03/2235068.html）后，感触很深，万丈高楼平地起基础的东东还是时刻准备着。

--------------先插入100w条数据用时4'50'’
declare @i int
set @i=0
while @i<1000000
begin
insert into pagetest select cast(floor (rand()*1000000 )as int ), left( newid() ,10),getdate()
set @i=@i+1
end 
-------------单机完全查询总耗时16500毫秒-17500毫秒左右
select *from dbo.pagetest
重点对row_number()分页方法单机测试(取99000-100000之间的1001条数据)
---------------分页方案1
select * from(select row_number() over (order by id) rownumber,* from pagetest )a 
where rownumber between 99000 and 100000
--------------分页方案2
select *from (select row_number() over (order by id))rownumber,*from pagetest)a
where rownumber>99000 and rownumber<=100000
-------------分页方案3
select top 1001 from (select row_number()over (order by id ) rownumber,*from pagetest)a 
where rownumber>=99000

declare @begintime datetime
declare @endtime datetime
select @begintime=getdate()
---sql
select * from(select row_number() over (order by id) rownumber,* from 
select @endtime=getdate()
select datediff(ms,@begintime,@endtime)as'毫秒'

5次方案
方案1 五次分别耗时
--436 436 440 420 423

方案2五次分别耗时
--453 436 436 436  436

方案3五次分别耗时
--483 436 470 466 483