SQL SERVER2012新分页方式

guguda2008 2012-03-19 07:42:50
加精
SQL SERVER2012在ORDER BY 子句中加入了新元素offset,允许用户在排序完成的结果集中自定义输出行范围,大大简化了分页SQL的书写方式和效率。以下是与以前的两种分页方式的简单对比
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了。

博文地址
http://blog.csdn.net/guguda2008/article/details/7370398
...全文
11896 点赞 收藏 169
写回复
169 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zhwITbird 2013-06-20
谢谢分享。。
回复
524929657雯 2013-06-15
我还是 2000wu.wu.
回复
更加简单适用了
回复
dy_stock 2013-05-30
不错,学习了
回复
King之主 2013-04-26
分页当初写了40多遍,现在这么一改,我勒个去,感觉以前写的真浪费了..........
回复
x-code-man 2013-04-24
support
回复
cainiao_w 2013-04-23
2005 和 2008 的路过
回复
guguda2008 2013-04-02
这谁又给挖出来了,我都忘了发过这帖。。。。
回复
感谢楼主分享
回复
chuanzhang5687 2013-03-28
回复
發糞塗牆 2013-03-28
回复
Cloud_Hero 2013-02-25
<p><strong><em><u>帖子对我太有帮助了
回复
nikolay 2013-02-23
学习了~~谢谢LZ分享!
回复
雨泽 2013-02-05
支持.顶
回复
songtongrun 2012-12-15
回复
taoge1992_02 2012-12-15
多看点前沿的技术还是很好的......光上课是不可能知道的
回复
leonlgw 2012-12-08
引用 41 楼 likevs 的回复:
呵呵,可惜公司的服务器用的是MS SQL2005 ROW_NUMBER分页方式 不过还是非常感谢楼主,至少让我心里清楚2012版本有这个新特性,很默记心中了
不是可惜,在SQL Server 2005用Row_Number来做分页,性能和维护性也很不错了。2012的新的Paging Query确实方便,性能方面应该也是有所提高的
回复
jia_guijun 2012-09-07
很好,不错的技术。
回复

不错,我喜欢,哈哈。
回复
maoxinrongkeke 2012-08-29
谢谢分享,还木有来得及去看2012呢
回复
发帖
新技术前沿
创建于2007-09-28

6124

社区成员

MS-SQL Server 新技术前沿
申请成为版主
帖子事件
创建了帖子
2012-03-19 07:42
社区公告
暂无公告