简单点分页

写很复杂的存储过程也许效率可能是高一点,但是很复杂,下面是很简单但效率也不错的分页方法:

set @ = 'select * from 
(select Top ' + cast(@Size as varchar(100)) + ' * from 
       (select Top ' + cast((@Page * @Size) as varchar(100)) + ' * from products) T1 
' + 'order by T1.productid desc) T2 
order by T2.productid'

取第40-50条记录(第5页) 
select * from (select top 10 * from (select  top 50 * from tb) T1 order by T1.id desc)T2
order by T2.id asc
或者:
select top 10 * from tb where id>(select max(id) from (select top 40 id  from tb order by id asc ))order by id asc
如果倒序:
select top 10 * from tb where id<(select min(id) from (select top 40 id  from tb order by id  desc))order by id desc