浅析SQL Server的分页方式 ISNULL与COALESCE性能比较(2)-木庄网络博客

接下来我们看看二者最大的不同，我们通过计算列并且在其上面创建主键或者非空约束，看看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！

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

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » 浅析SQL Server的分页方式 ISNULL与COALESCE性能比较

浅析SQL Server的分页方式 ISNULL与COALESCE性能比较

数据库系统概念第6版

相关推荐

评论