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