msdn 上相关介绍: 点击打开链接
语法:
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,...n ]
[ <offset_fetch> ]
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
大家熟悉的SQL Server2005 的分页方式是 row_number , 但这个分页一般来说需要嵌套一次。
下面就做个最简单的对比, 顺便了解一下新分页的写法。
DECLARE @pageIndex INT,@pageSize INT
SET @pageIndex=2
SET @pageSize=5
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY number) AS rid,sv.number
FROM [master].dbo.spt_values AS sv
WHERE sv.type='P' AND sv.number>0
) AS t
WHERE t.rid >(@pageIndex-1)*@pageSize AND t.rid<=@pageIndex*@pageSize
ORDER BY t.rid
/*
rid number
6 6
7 7
8 8
9 9
10 10
*/
SELECT sv.number
FROM [master].dbo.spt_values AS sv
WHERE sv.type='P' AND sv.number>0 ORDER BY sv.number
OFFSET (@pageIndex-1)* @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
/*
number
6
7
8
9
10
*/
下面以 AdventureWorks2014 库为测试库, 以较大的一个表 Person.Person 来详细对比分页的情况:
row_nubmer 分页 SQL:
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @pageIndex INT=800,@pageSize INT=20
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY lastname) AS rid,* FROM Person.Person
) AS t
WHERE t.rid >(@pageIndex-1) * @pageSize AND t.rid<=@pageIndex*@pageSize
ORDER BY t.rid
/*
---------- @pageIndex=1 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 64 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 127 毫秒。
---------- @pageIndex=2 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 125 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 114 毫秒。
---------- @pageIndex=800 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 49088 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 78 毫秒,占用时间 = 168 毫秒。
*/
offset fetch 分页:
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @pageIndex INT=800,@pageSize INT=20
SELECT * FROM Person.Person AS t
ORDER BY t.lastname
OFFSET (@pageIndex-1)*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
/*
---------- @pageIndex=1 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 65 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 43 毫秒。
---------- @pageIndex=2 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 125 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 106 毫秒。
---------- @pageIndex=800 ----------
(20 行受影响)
表 'Person'。扫描计数 1,逻辑读取 49088 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 63 毫秒,占用时间 = 175 毫秒。
*/
执行计划对比:
row_nubmer:
offset fetch:
前几页有优势, 后面也差不多吧。