SQL SERVER2012在ORDER BY 子句中加入了新元素offset,允许用户在排序完成的结果集中自定义输出行范围，大大简化了分页SQL的书写方式和效率。以下是与以前的两种分页方式的简单对比
1 use master
2 go
3 set nocount on
4 go
5 set showplan_text on
6 go
7 --2012的OFFSET分页方式
8 select number
9 from spt_values
10 where type='p'
11 order by number offset 10 rows fetch next 5 rows only;
12 go
13 --2005的ROW_NUMBER分页方式
14 select number from (
15 select number,row_number() over(order by number) as num from spt_values where type='p'
16 ) t where num between 11 and 15
17 order by number asc
18 go
19 --2000的TOP分页方式
20 select number from (
21 select top 5 number from (
22 select top 15 number from spt_values where type='p' order by number asc
23 ) t
24 order by number desc
25 ) t
26 order by number asc
27 go
28 set showplan_text off
29 go
30 /*
31 StmtText
32 ----------------------------------------------------------------------------------------------------------
33 select number
34 from spt_values
35 where type='p'
36 order by number offset 10 rows fetch next 5 rows only;
37
38 StmtText
39 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
40 |--Top(OFFSET EXPRESSION:((10)),TOP EXPRESSION:((5)))
41 |--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)
42
43 StmtText
44 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
45 select number from (
46 select number,row_number() over(order by number) as num from spt_values where type='p'
47 ) t where num between 11 and 15
48 order by number asc
49
50 StmtText
51 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
52 |--Sort(ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] ASC))
53 |--Filter(WHERE:([Expr1005]>=(11) AND [Expr1005]<=(15)))
54 |--Top(TOP EXPRESSION:(CASE WHEN (15) IS NULL OR (15)<(0) THEN (0) ELSE (15) END))
55 |--Sequence Project(DEFINE:([Expr1005]=row_number))
56 |--Segment
57 |--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)
58
59 StmtText
60 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
61 select number from (
62 select top 5 number from (
63 select top 15 number from spt_values where type='p' order by number asc
64 ) t
65 order by number desc
66 ) t
67 order by number asc
68
69 StmtText
70 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
71 |--Sort(ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] ASC))
72 |--Sort(TOP 5, ORDER BY:([mssqlsystemresource].[sys].[spt_values].[number] DESC))
73 |--Top(TOP EXPRESSION:((15)))
74 |--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)
75
76 */
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
/*
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)
*/
