浅析SQL Server的分页方式 ISNULL与COALESCE性能比较
前言
上一节我们讲解了数据类型以及字符串中几个需要注意的地方,这节我们继续讲讲字符串行数同时也讲其他内容和穿插的内容,简短的内容,深入的讲解。(可参看文章《详解sql server中的数据类型》)
分页方式
在sql 2005或者sql 2008中我们是利用row_number开窗函数来进行分页的,关于开窗函数,我们在sql进阶中会详细讲讲。如下:
use tsql2012 go declare @startrow int declare @endrow int set @startrow = 31 set @endrow = 40 select [address], [city], [region] from ( select [sc].[address], [sc].[city], [sc].[region], row_number() over( order by [sc].[address], [sc].[city],[sc].[custid]) as rownumber from sales.customers sc) salecustomer where rownumber > @startrow and rownumber < @endrow order by [address], [city], [region];
上面代码想必就不需要我解释了,同时利用视图也可以进行分页
with salecustomer as ( select [sc].[address], [sc].[city], [sc].[region], row_number() over( order by [sc].[address], [sc].[city],[sc].[custid]) as rownumber from sales.customers sc) select [address], [city], [region] from salecustomer where rownumber > @startrow and rownumber < @endrow order by [address], [city], [region] go
下面我们来看看这二者利用sql查询语句和视图有没有性能上的差异呢?来,检验就是。
从这里我们可以看出,二者在性能开销上并没有什么不同,大部分情况下应该是一样的。但是在sql 2011之后版本则出现新的语法来实现分页,估计我们依然还是利用的row_number,可能是为了能够兼容sql版本到2005,利用offset-fetch来进行筛选过滤,它是基于sql 2011之后才有。上述我们是取从31到40之间的数据,如果用offset-fetch实现,我们看这个函数字面意思就能知道跳过多少数据然后抓取多少数据,所以我们需要跳过前面30条数据,需要取接下来10条数据。
use tsql2012 go declare @pagesize int = 10, @pageindex int = 3 select * from sales.customers order by custid offset @pageindex * @pagesize rows fetch next 10 rows only go
如果对sql版本要求不低的话,利用offset-fetch来实现简直爽爆了。
coalesce compare to isnull
上一节我们讲了讲一些字符串函数,其中漏掉了一个字符串函数即coalesce,这个函数在sql 2008+上才有,其中还有其他几个类似对字符串函数的处理,我们一起来看下。msdn对其定义为:按顺序计算变量并返回最初不等于 null 的第一个表达式的当前值。返回数据类型优先级最高的 expression 的数据类型。 如果所有表达式都不可为 null,则结果的类型也不可为 null。如果所有参数均为 null,则 coalesce 返回 null。至少应有一个 null值为null 类型。说白了就是对null的处理。我们简单来看下一个例子。
use tsql2012 go select custid, country, region, city, country + coalesce(n''+ region, n'') + n',' + city as location from sales.customers
上述我们可以看到,我们通过coalesce函数来对null用空字符串来代替进行处理。sql 2012也引入了concat函数来接收一个要连接的输入列表并自动以空字符串替换null,上述同样可以用concat函数来代替。
use tsql2012 go select custid, country, region, city, country + concat(country,n''+ region, n',' + city) as location from sales.customers
同时我们看到下图知道,concat函数参数至少要有两个:
msdn对concat函数解释为:concat 采用可变数量的字符串参数,并将它们串联成单个字符串。 它需要至少两个输入值;否则将引发错误。 所有参数都隐式转换为字符串类型,然后串联在一起。 null 值被隐式转换为空字符串。 如果所有参数都为 null,则返回 varchar(1) 类型的空字符串。 隐式转换为字符串的过程遵循现有的数据类型转换规则。
我们继续回到coalesce函数,主要看看它与isnull函数的区别。
coalesce与isnull函数探讨
可能有些人认为isnull比coalesce函数更快,或者有人认为isnull和coalesce函数是等同,更有人认为应该倾向于使用coalesce函数,因为它是 ansi sql标准函数。认为归认为,那么两者到底有何不同呢,我们一起来看下。
(1)coalesce和isnull处理数据类型优先不同
coalesce函数决定类型输出基于数据类型优先【data type precedence】,所以如下在处理int时,datetime优先级高于int。
declare @int int, @datetime datetime; select coalesce(@datetime, 0); select coalesce(@int, current_timestamp);
而对于isnull函数,数据类型不受数据类型优先影响,而是通过函数参数列表第一项影响,isnull在于交换而coalesce在于所有参数查询的合并。
declare @int int, @datetime datetime; select isnull(@datetime, 0);
我们看看进行如下操作会如何
declare @int int, @datetime datetime; select isnull(@int, current_timestamp);
此时会出现无法将datetime转换为int
此时我们需要显式进行如下转换才行
declare @int int, @datetime datetime; select isnull(@int, convert(int,current_timestamp)); select isnull(@int, cast(current_timestamp as int));
(2)isnull会造成数据丢失
我们再来看二者的对比的例子
declare @c5 varchar(5); select 'coalesce', coalesce(@c5, 'jeffcky wang') union all select 'isnull', isnull(@c5, 'jeffcky wang');
上述我们定义字符串变量长度为5,而利用isnull字符串却被截取了,在这里我们可以认为isnull会导致数据的丢失而非出错。为什么会出现这样的结果呢?上述我们已经讲过isnull受第一个参数影响,其长度定义为5,所以只能为5,这是会进行截取,而coalesce函数着眼于检测所有元素,此时为12所以会完全进行返回。我们通过运行如下就可以看出。
declare @c5 varchar(5); select c = coalesce(@c5, 'jeffcky wang'), i = isnull(@c5, 'jeffcky wang') into dbo.testisnull_coalesce select name, t = type_name(system_type_id), max_length, is_nullable from sys.columns where [object_id] = object_id('dbo.testisnull_coalesce');
我们看到上述coalesce合并的结果是可空的而isnull不是,有一点点不同。
(3)coalesce对列计算时需要持久化
接下来我们看看二者最大的不同,我们通过计算列并且在其上面创建主键或者非空约束,看看isnull和coalesce的区别
create table dbo.createisnull ( a int, b as isnull(a, 15) primary key );
我们再来看看coalesce函数来计算列
create table dbo.createcoalesce ( a int, b as coalesce(a, 15) primary key );
很明显我们需要对列进行持久化,通过添加persisted关键字,如下即可。
create table dbo.createcoalesce ( a int, b as coalesce(a, 15) persisted primary key );
我们再来看看一个二者的不同
declare @c char(10); select 'x' + coalesce(@c, '') + 'y'; select 'x' + isnull(@c, '') + 'y';
我们到这里其实我们可以稍微概括下二者的区别:isnull着重于替换,而coalesce着重于合并。coalesce显示忽略了null并用空字符串填充并压缩,而isnull对null会用空字符串填充但不会压缩。
(4)coalesce函数支持超过两个参数
对于多个参数输入,isnull函数需要嵌套调用,而coalesce能够处理任何数量,至于上限不知,所以对于多个参数使用coalesce更加,如下使用多个参数输入。
select coalesce(a, b, c, d, e, f, g) from dbo.table;
而对于isnull,我们需要这样做
select isnull(a, isnull(b, isnull(c, isnull(d, isnull(e, isnull(f, g)))))) from dbo.table;
二者最终执行时和利用case一样
case when [tempdb].[dbo].[table].[a] is not null then [tempdb].[dbo].[table].[a] else case when [tempdb].[dbo].[table].[b] is not null then [tempdb].[dbo].[table].[b] else case when [tempdb].[dbo].[table].[c] is not null then [tempdb].[dbo].[table].[c] else case when [tempdb].[dbo].[table].[d] is not null then [tempdb].[dbo].[table].[d] else case when [tempdb].[dbo].[table].[e] is not null then [tempdb].[dbo].[table].[e] else case when [tempdb].[dbo].[table].[f] is not null then [tempdb].[dbo].[table].[f] else [tempdb].[dbo].[table].[g] end end end end end end
(5)coalesce和isnull二者性能比较
我们来运行如下查询
dbcc dropcleanbuffers; declare @a varchar(5), -- = 'str_a', -- this line changed per test @b varchar(5), -- = 'str_b', -- this line changed per test @v varchar(5), @x int = 0, @time datetime2(7) = sysdatetime(); while @x <= 500000 begin set @v = coalesce(@a, @b); --coalesce set @x += 1; end select datediff(millisecond, @time, sysdatetime()); go dbcc dropcleanbuffers; declare @a varchar(5), -- = 'str_a', -- this line changed per test @b varchar(5), -- = 'str_b', -- this line changed per test @v varchar(5), @x int = 0, @time datetime2(7) = sysdatetime(); while @x <= 500000 begin set @v = isnull(@a, @b); --isnull set @x += 1; end select datediff(millisecond, @time, sysdatetime());
我们有查询四个场景:(1)两个参数都为null(2)第一个参数为null(3)第二个参数为null(4)两个参数都为null。每个场景测试十次,最终得出如下结果
从上看出二者性能并未有什么太大差异,我们不需要太担心了吧,当然上述场景并未完全覆盖,至少还是能说明一部分。上述我们得到的结果查看的执行时间,现在我们再来看看二者查询执行计划。
select coalesce((select max(index_id) from sys.indexes where [object_id] = t.[object_id]), 0) from sys.tables as t; select isnull((select max(index_id) from sys.indexes where [object_id] = t.[object_id]), 0) from sys.tables as t;
上述可能不太准确,还和硬件配置有关,也有可能coalesce的性能差与isnull。二者性能应该是没什么很大差异。
(6)isnull和自然语言描述不一致
为何是和自然语言描述不一致呢?也就是说我们当判断某个值为null会做什么,不为null再做什么,这时用查询语言sql描述如下:
if isnull(something) -- do something
我们用自然语言角度来看,翻译为如果something为null我们做什么,这个时候是不一致的。因为在sql server中没有布尔值类型,上述我们只能进行如下转换
if something is null -- do something -- or if isnull(something, null) is null -- do something -- or if isnull(something, '') = '' -- do something
(7)利用guid看看奇葩的isnull
在本节介绍之前我们再来看看一个例子,从而颠覆你的想法,让你发狂。
select isnull(newid(), 'jeffckywang') as col1
这样看是没问题,我们将其插入到表中,再看对其列的描述
select isnull(newid(), 'jeffckywang') as col1 into dbo.isnullexample2; exec sp_help 'dbo.isnullexample2';
表中数据确实存在,但是对列的描述是可空的。
总结
上述重点讲述了coalesce和isnull函数区别之处,通过本节的讲述二者的场景和区别,我们是不是应该有了一点想法,到底是该用coalesce还是isnull呢?大部分情况下还是利用coalesce为好,一是此函数是作为sql标准函数,第二个相对于isnull它可以支持更多参数,而isnull则需要嵌套,而对于isnull难道就没有应用场景了吗,当然有在查询数据时判断数据是否为null,这种情况下利用isnull,例如,如下
select isnull(argument, '') into dbo.isnullexample;
本文关于isnull和coalesce的比较参考文章:deciding between coalesce and isnull in sql server。本节我们到此结束,简短的内容,深入的理解,我们下节再会,good night!
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持!