sql分页存储过程比较

一，先创建一百万条数据

--1000000
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是最合适之选。