一，先创建一百万条数据
--
drop table #tmp
create table #tmp
(
id int identity(1,1) primary key,
name varchar(20)
)
declare @i int
set @i = 0
while @i<1000000
begin
insert into #tmp
select 'abc'+ cast(@i as varchar)
set @i = @i + 1
end
二，测试
1，not in
declare @PageIndex int ,@PageSize int
declare @timediff datetime
set @PageIndex =1000
set @PageSize = 10
set @timediff = getdate()
select top(@PageSize) id,name from #tmp
where id not in(
select top (@PageSize * (@PageIndex -1))id from #tmp
order by id)
order by id
select datediff(ms,@timediff,getdate())
2，max
--max
declare @PageIndex int ,@PageSize int
declare @timediff datetime
set @PageIndex =50000
set @PageSize = 10
set @timediff = getdate()
select top(@PageSize) id,name from #tmp
where id >(
select max(id) as id from(
select top (@PageSize * (@PageIndex -1)) id as id from #tmp
order by id) a )
order by id
select datediff(ms,@timediff,getdate())
3，rowNumber
--rownumber
declare @PageIndex int ,@PageSize int
declare @timediff datetime
set @PageIndex =100000
set @PageSize = 10
set @timediff = getdate();
with cte as
(
select *,row_number() over(order by id) as rowNo from #tmp
)
select * from cte
where rowNo between (@PageIndex -1)*@PageSize and @PageIndex*@PageSize
select datediff(ms,@timediff,getdate())
4，表中有多个主键时，top的写法要注意，而rowNumber则不存在这个问题。
-- top 多个主键是不能用not in，只好用not exists
declare @PageIndex int ,@PageSize int
set @PageIndex = 10000
set @PageSize = 10; select top (@PageSize) * from dbo.DQuestionData a
where not exists (
select 1
from
(select top ((@PageIndex-1) * @PageSize) * from dbo.DQuestionData order by SHOP_NO) b
where a.ANS_DATETIME = b.ANS_DATETIME
and a.SHOP_NO = b.SHOP_NO
and a.TERMINAL_ID = b.TERMINAL_ID
and a.ANS_POSITION = b.ANS_POSITION
and a.QUESTION = b.QUESTION
and a.ANSWER = b.ANSWER )
order by SHOP_NO; -- ROW_NUMBER()
declare @PageIndex int ,@PageSize int,@timediff datetime
set @PageIndex = 10000
set @PageSize = 10;
set @timediff = GETDATE();
with cte_tmp as
(
select *,ROW_NUMBER() over(order by SHOP_NO) as rowNo
from (
select * from dbo.DQuestionData
) a
)
select * from cte_tmp
where rowNo between (@PageIndex -1) * @PageSize + 1 and @PageIndex * @PageSize
select DATEDIFF(ms,@timediff,getdate())
5 ，用rownumber定义一个可以返回页数的存储过程
create procedure GetPaging
(
@PageIndex int = 1,
@PageSize int = 10,
@PageCount int output
)
as
begin
select @PageCount = ceiling(count(*) /cast(@PageSize as float)) from testTable;
with cte as
(
select top (@PageSize * (@PageIndex -1))id from testTable
order by id
)
select top(@PageSize) id,name from testTable
where id not in(
select id from cte)
order by id
end
三，结论
1，not in 随着PageIndex增大，速度越慢，不可用。另：如果用exec（@sql）的方式执行，速度就很快，不知道啥原因。
2，max如果用到索引列，数度很快，随着PageIndex增大，有小幅变慢。
3，rownumber速度适中，并且基本是匀速的，且适用范围广，写法简单。
平常应用，rownumber是最合适之选。