sql - 二进制转换的意外行为(COALESCE 与 ISNULL)

Question

您能否评论下所示的哪种方法更可取？我希望这个问题不会因为“自以为是”而被屏蔽。我愿意相信有一个解释可以清楚地说明这一点。

上下文:我有一个代码可以将第 3 方表内容镜像到我自己的表(优化)。它完美地工作了一段时间，直到数据库的大小/修改达到某个阈值。

优化基于更多表的行版本值，并记住源表中的最大值。通过这种方式，我可以逐步更新我的本地表，这比不时从头开始重建要快得多。

当 row-version 值超过 4byte 值时，问题开始出现。经过一些努力，我发现 binary(8) 值的高 4 个字节被设置为 0。后来，发现嫌疑人有一个形式 COALESCE(MAX(row_version) , 1)。

COALESCE 用于覆盖本地表是新的，现在包含数据的情况——用于比较源表的 MAX(row_version) 和一些有意义的东西。

显示错误的示例:为了模拟最后提到的情况，我想将 binary(8) 列的 NULL 值转换为 1。我是还添加了稍后添加的 ISNULL 用法。原始代码仅包含 COALESCE。

DECLARE @bin8null binary(8) = NULL

SELECT 'bin NULL' AS the_variable, @bin8null AS value
SELECT 'coalesce 1' AS op, COALESCE(@bin8null, 1) AS good_value
SELECT 'coalesce 1 + convert' AS op, CONVERT(binary(8), COALESCE(@bin8null, 1)) AS good_value
SELECT 'isnull 1' AS op, ISNULL(@bin8null, 1) AS good_value
SELECT 'isnull 0x1' AS op, ISNULL(@bin8null, 0x1) AS bad_value

(图像coalesce 0x1 + convert中存在错误，稍后在代码中修复为coalesce 1 + convert，但图像中未修复。)

当二进制值大于可存储在 4 个字节中的部分时，会出现应用程序错误。这里使用了 0xAAAAAAAA。 (实际上，0x00000001就是这样，很难看出是单个的1变成了0。)

DECLARE @bin8 binary(8) = 0xAAAAAAAA01BB3A35

SELECT 'bin' AS the_variable, @bin8 AS value
SELECT 'coalesce 1' AS op, COALESCE(@bin8, 1) AS bad_value
SELECT 'coalesce 1 + convert' AS op, CONVERT(binary(8), COALESCE(@bin8, 1)) AS bad_value
SELECT 'coalesce 0x1 + convert ' AS op, CONVERT(binary(8), COALESCE(@bin8, 0x1)) AS good_value
SELECT 'isnull 1' AS op, ISNULL(@bin8, 1) AS good_value
SELECT 'isnull 0x1' AS op, ISNULL(@bin8, 0x1) AS good_value

在 MS-SQL Server 2014 上的 Microsoft SQL Server Management Studio 中执行时，结果如下所示:

描述 -- 我的理解:COALESCE() 似乎是从最后处理的参数的类型派生出结果的类型。这样，非 NULL binary(8) 被转换为 int，这导致高 4 字节丢失。 (见图中第2和第3个红色bad_value，两种情况的区别仅在于十进制/十六进制的显示形式。)

另一方面，ISNULL() 似乎保留了第一个参数的类型，并将第二个值转换为该类型。应该仔细理解 binary(8) 更像是一系列字节。作为一个大整数的解释只是解释。因此，作为默认值的 0x1 不会扩展为 8 字节整数并产生错误值。

我的解决方案: 因此，我已使用 ISNULL(MAX(row_version), 1) 修复了该错误。对吗？

Answer 1

这不是错误。他们被记录为处理 data type precedence不同的。 COALESCE 根据检查所有 参数确定输出的数据类型，而 ISNULL 有一种更简单的方法，只检查第一个参数。 (两者仍然需要包含完全兼容的值，这意味着它们都可能转换为确定的输出类型。)

来自COALESCE topic :

Returns the data type of expression with the highest data type precedence.

ISNULL topic没有以相同的方式进行这种区分，而是隐式声明第一个表达式确定类型:

replacement_value must be of a type that is implicitly convertible to the type of check_expression.

我有一个类似的例子(并描述了 COALESCE 和 ISNULL 之间的其他几个区别)here .基本上:

DECLARE @int int, @datetime datetime;
SELECT COALESCE(@int, CURRENT_TIMESTAMP);

-- works because datetime has a higher precedence than the chosen output type, int
2020-08-20 09:39:41.763

GO
DECLARE @int int, @datetime datetime;
SELECT ISNULL(@int, CURRENT_TIMESTAMP);

-- fails because int, the first (and chosen) output type, has a lower precedence than datetime
Msg 257, Level 16, State 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.