浅析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！

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，同时也希望多多支持！