原文见 : http://topic.csdn.net/u/20120319/19/fb516bd9-f600-430a-8d0f-ed6a911f4ca2.html
SQL SERVER2012在ORDER BY 子句中加入了新元素offset,允许用户在排序完成的结果集中自定义输出行范围，大大简化了分页SQL的书写方式和效率。以下是与以前的两种分页方式的简单对比
use master
go
set nocount on
go
set showplan_text on
--//当 SET SHOWPLAN_TEXT 为 ON 时，SQL Server 返回各个 Transact-SQL 语句的执行信息但不执行语句,为 OFF 时，SQL Server 执行语句但不生成报表。
go
--2012的OFFSET分页方式
select number
from spt_values
where type='p'
order by number offset 10 rows fetch next 5 rows only;
--在结果中偏移10行，即从第11行[含]开始只遍历5行
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
/*
StmtText
----------------------------------------------------------------------------------------------------------
select number
from spt_values
where type='p'
order by number offset 10 rows fetch next 5 rows only;
StmtText
----------------------------------------------------------------------------------------------------------
|--Top(OFFSET EXPRESSION:((10)),TOP EXPRESSION:((5)))
|--Index Scan(OBJECT:([mssqlsystemresource].[sys].[spt_values].[ix2_spt_values_nu_nc]), WHERE:(CONVERT(nchar(3),[mssqlsystemresource].[sys].[spt_values].[type],0)=N'p') ORDERED FORWARD)
StmtText
----------------------------------------------------------------------------------------------------------
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
StmtText
-----------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] ASC))
|--Filter(WHERE:([Expr1005]>=(11) AND [Expr1005]<=(15)))
|--Top(TOP EXPRESSION:(CASE WHEN (15) IS NULL OR (15)<(0) THEN (0) ELSE (15) END))
|--Sequence Project(DEFINE:([Expr1005]=row_number))
|--Segment
|--Index Scan(OBJECT:([mssqlsystemresource].[sys].[spt_values].[ix2_spt_values_nu_nc]), WHERE:(CONVERT(nchar(3),[mssqlsystemresource].[sys].[spt_values].[type],0)=N'p') ORDERED FORWARD)
StmtText
-----------------------------------------------------------------------------------------------------------
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
StmtText
------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] ASC))
|--Sort(TOP 5, ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] DESC))
|--Top(TOP EXPRESSION:((15)))
|--Index Scan(OBJECT:([mssqlsystemresource].[sys].[spt_values].[ix2_spt_values_nu_nc]), WHERE:(CONVERT(nchar(3),[mssqlsystemresource].[sys].[spt_values].[type],0)=N'p') ORDERED FORWARD)
*/
从查询计划来看，2012的查询计划中，offset直接操作了top迭代器，分页计划十分简单，看起来也比后两种简单明了。
而在系统预估的查询开销中，OFFSET占9%，ROW_NUMBER占45%，TOP占47%（加起来101% -_-||）,OFFSET占了绝对的优势。
在2008的更新中，我最喜欢的是MERGE，2012中就是OFFSET了。
博文地址
http://blog.csdn.net/guguda2008/article/details/7370398