为什么 T-SQL 的 ISNULL() 函数会截断字符串而 COALESCE 函数不会？

Question

鉴于以下情况：

SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?)
SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ

为什么这些语句返回不同的结果？

- natenho

Answer 1

根据微软文档，对于这个函数：

ISNULL(check_expression, replacement_value)

replacement_value必须是一种可以隐式转换为check_expression类型的类型。请注意，'xy'+NULL的类型为VARCHAR(3)。因此，您的字符串'ABCDEFGHIJ'被强制转换为VARCHAR(3)，因此被修剪。

这听起来很奇怪，为什么不是VARCHAR(2)，但事实就是这样-比'xy'多一个字符。您可以使用这个SQLFiddle进行试验，看看'xy'+NULL的类型与表达式CASE WHEN 1=2 THEN 'XYZ' ELSE NULL END相同，后者是NULL，但隐式兼容于VARCHAR(3)。

对于表达式'xy'+NULL，感知长度似乎可以计算为'xy'字符串长度（2）加上每个NULL添加的1。例如，'xy'+NULL+NULL的类型为VARCHAR(4)，'xy'+NULL+NULL+NULL的类型为VARCHAR(5)，依此类推-请参见这个SQLFiddle。这非常奇怪，但这就是MS SQL Server 2008和2012的工作方式。

- mvp

---

解释得很好，但我不相信varchar(3)而不是varchar(2) - 我找不到任何文件记录它。 - natenho

我必须增加这里的信息，以更详细地解释COALESCE行为相对于ISNULL行为的差异，来自Microsoft文档（http://technet.microsoft.com/en-us/library/ms190349.aspx）： COALESCE表达式是CASE表达式的语法快捷方式。也就是说，代码COALESCE(expression1,...n)被查询优化器重写为以下CASE表达式：CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2... ELSE expressionN END -> CASE返回类型由最高expr优先级给出。 - natenho

这解释了COALESCE的行为，但并没有说明为什么'xy'+NULL的类型是VARCHAR(3)。这一定与SQL Server内部有关。 - mvp

我不确定我是否能从你的示例中得到证明。第二个查询是一个CASE，因为有'XYZ'，而不是NULL，所以我猜剩下的疑问是关于'X'+NULL变成varchar(2)而不是varchar(1)。那么，在这种情况下，NULL被认为是单个字符吗？当使用ISNULL连接字符串时，我需要担心溢出/截断吗？ - natenho

是的，NULL会增加1个字符。这非常奇怪，但这里有确凿的证据：http://sqlfiddle.com/#!3/d41d8/20994 - mvp

关于 'AA' + NULL + NULL 的有趣细节 - 我向 MicrosoftDocs/sql-docs 提交了一份 PR 来记录这种行为。我在使用 SQL Server 工作的 11 年中从未见过这种行为。 - John Zabroski

Answer 2

您可以在这里查看所有的差异，非常明显。

MSDN: http://msdn.microsoft.com/zh-cn/library/ms190349.aspx MSDN 博客: http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

- Viji

---

对于这篇博客文章点个赞，我在第一次调研时没有注意到它。非常好的资源。 - natenho

Answer 3

ISNULL() 将替换值转换为检查表达式的类型。在本例中，检查表达式的类型是 CHAR(2)，因此将替换值转换会将其截断（您确定获取到的是 ABC 而不仅仅是 AB 吗？）。

来自 Microsoft 文档：

如果 replacement_value 长度超过 check_expression，则可能会截断 replacement_value。

- Barmar