MS Sql 分页

Row_number 分页

select * from (select row_number() over(order by id) as rownumber,id from test) as d

　　where rownumber >(CurrentPageIndex - 1) * PageSize  and rownumber<=CurrentPageIndex * PageSize)

适用于sql 2005 及以上

 

TOP分页

（1）

select top PageSize * from test

　　where id>(select max(id) from

　　　　(select top PageSize*(CurrentPageIndex - 1) from test order by id ) as t)

 

（2）

select * from

　　(select top PageSize * from

　　　　( select top  PageSize * CurrentPageIndex * from test order by id asc)

　　t1 order by id desc )t2

order by id asc

 

效率 当大数据量时 Row_number 分页》top（1）》top（2）