SQLserver 202 新增加的功能,在ORDER BY 子句中新增 OFFSET 和 FETCH 子句,可以实现分页查询效果。
ORDER BY 子句中的语法如下:(参考:ORDER BY 子句 (Transact-SQL))
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
]
}
<offset_fetch> 子句中:
FIRST 和 NEXT 是同义词,是为了与 ANSI 兼容而提供的。ROW 和 ROWS 是同义词,是为了与 ANSI 兼容而提供的。
模拟测试:
-- drop table dbo.TestTab
create table dbo.TestTab
(
id int identity(1,1) not null primary key clustered,
dtime datetime default(getdate())
)
go
set nocount on
insert into dbo.TestTab default values
go 50000
set nocount off
select count(*) from dbo.TestTab(nolock)
分布对比常用的一种分页查询:
对比查询1~100,20001~20100,49001~49100 行的情况。
SELECT id,dtime FROM dbo.TestTab
ORDER BY id
OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY
SELECT id,dtime FROM (
SELECT ROW_NUMBER()OVER(ORDER BY id ASC) AS orderid,id,dtime
FROM dbo.TestTab
) TAB
WHERE orderid BETWEEN 1 AND 100
ORDER BY orderid
结果如下:IO 是一样的(由于查询时间都为0,估计也不准,暂不统计。)
| 1~100 | 5001~5100行 | 9900~10000行 | 估计行数 | |
OFFSET FETCH | 开销占比 | 49% | 84% | 90% | 100 |
ROW_NUMBER | 开销占比 | 51% | 16% | 10% | 9 |
ROW_NUMBER 在 编译内存,CPU 比 OFFSET FETCH 多。
上面统计中:OFFSET FETCH 查询的记录在表中越靠后,开销反而更大,而这个的估计行数是准确的。
OFFSET FETCH 的一个好处是简化了分页查询语句!其他有待测试!~