SQL SERVER2012在ORDER BY 子句中加入了新元素offset,允许用户在排序完成的结果集中自定义输出行范围,大大简化了分页SQL的书写方式和效率。以下是与以前的两种分页方式的简单对比
从查询计划来看,2012的查询计划中,offset直接操作了top迭代器,分页计划十分简单,看起来也比后两种简单明了。
而在系统预估的查询开销中,OFFSET占9%,ROW_NUMBER占45%,TOP占47%(加起来101% -_-||),OFFSET占了绝对的优势。
在2008的更新中,我最喜欢的是MERGE,2012中就是OFFSET了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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)
*/
|
从查询计划来看,2012的查询计划中,offset直接操作了top迭代器,分页计划十分简单,看起来也比后两种简单明了。
而在系统预估的查询开销中,OFFSET占9%,ROW_NUMBER占45%,TOP占47%(加起来101% -_-||),OFFSET占了绝对的优势。
在2008的更新中,我最喜欢的是MERGE,2012中就是OFFSET了。