sqlserver2012 分页比较

SQL SERVER2012在ORDER BY 子句中加入了新元素offset,允许用户在排序完成的结果集中自定义输出行范围，大大简化了分页SQL的书写方式和效率

use master  
go  
set nocount on  
go  
set showplan_text on  
go  
  
--2012的OFFSET分页方式  
select number   
from spt_values  
where type='p'  
order by number offset 10 rows fetch next 5 rows only;  
go  
  
--2005的ROW_NUMBER分页方式  
select number from (  
select number,row_number() over(order by number) as num from spt_values where type='p'  
) t where num between 11 and 15  
order by number asc  
go  
  
--2000的TOP分页方式  
select number from (  
select top 5 number from (  
select top 15 number from spt_values where type='p' order by number asc  
) t  
order by number desc  
) t   
order by number asc  
go  
set showplan_text off  
go