前言
上一节我们讲解了数据类型以及字符串中几个需要注意的地方，这节我们继续讲讲字符串行数同时也讲其他内容和穿插的内容，简短的内容，深入的讲解。(可参看文章《详解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！
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，同时也希望多多支持www.887551.com！