--

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 

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())

--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())

--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())

-- 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())

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