Sql Server的ISNULL（）函数是懒惰/短路的吗？

TIs ISNULL() a lazy function?

TI ISNULL（）是一个懒惰的函数？

That is, if i code something like the following:

也就是说，如果我编写如下代码：

SELECT ISNULL(MYFIELD, getMyFunction()) FROM MYTABLE

SELECT ISNULL（MYFIELD，getMyFunction（））来自MYTABLE

will it always evaluate getMyFunction() or will it only evaluate it in the case where MYFIELD is actually null?

它会一直评估getMyFunction（）还是仅在MYFIELD实际为空的情况下评估它？

4 个解决方案

#1

It's whichever it thinks will work best.

这是它认为最好的效果。

Now it's functionally lazy, which is the important thing. E.g. if col1 is a varchar which will always contain a number when col2 is null, then

现在它在功能上是懒惰的，这是重要的。例如。如果col1是一个varchar，当col2为null时，它总是包含一个数字，那么

isnull(col2, cast(col1 as int))

Will work.

将工作。

However, it's not specified whether it will try the cast before or simultaneously with the null-check and eat the error if col2 isn't null, or if it will only try the cast at all if col2 is null.

但是，没有指定它是否会在null检查之前或同时尝试强制转换，如果col2不为null，则会吃掉错误，或者如果col2为null，它将只尝试强制转换。

At the very least, we would expect it to obtain col1 in any case because a single scan of a table obtaining 2 values is going to be faster than two scans obtaining one each.

至少，我们希望它在任何情况下都能获得col1，因为获得2个值的表的单次扫描将比两次扫描获得一个更快。

The same SQL commands can be executed in very different ways, because the instructions we give are turned into lower-level operations based on knowledge of the indices and statistics about the tables.

可以以非常不同的方式执行相同的SQL命令，因为我们给出的指令基于索引的知识和关于表的统计信息而变成较低级别的操作。

For that reason, in terms of performance, the answer is "when it seems like it would be a good idea it is, otherwise it isn't".

出于这个原因，就性能而言，答案是“当它看起来好像是一个好主意，否则就不是”。

In terms of observed behaviour, it is lazy.

就观察到的行为而言，它是懒惰的。

Edit: Mikael Eriksson's answer shows that there are cases that may indeed error due to not being lazy. I'll stick by my answer here in terms of the performance impact, but his is vital in terms of correctness impact across at least some cases.

编辑：Mikael Eriksson的回答显示，由于不是懒惰，有些情况可能确实是错误的。在性能影响方面，我会坚持我的答案，但至少在某些情况下，他对正确性影响至关重要。

#2

This works fine

这很好用

declare @X int
set @X = 1
select isnull(@X, 1/0)

But introducing an aggregate will make it fail and proving that the second argument could be evaluated before the first, sometimes.

但是引入聚合会使它失败并证明第二个参数可以在第一个参数之前进行评估，有时候。

declare @X int
set @X = 1
select isnull(@X, min(1/0))

#3

Judging from the different behavior of

从不同的行为来判断

SELECT ISNULL(1, 1/0)

SELECT ISNULL(NULL, 1/0)

the first SELECT returns 1, the second raises a Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered. error.

第一个SELECT返回1，第二个SELECT遇到Msg 8134，Level 16，State 1，Line 4除以零错误。错误。

#4

This "lazy" feature you are referring to is in fact called "short-circuiting"
And it does NOT always work especially if you have a udf in the ISNULL expression.
Check this article where tests were run to prove it:
Short-circuiting (mainly in VB.Net and SQL Server)

你所指的这个“懒惰”功能实际上被称为“短路”。如果你在ISNULL表达式中有一个udf，它并不总是有效。查看本文运行测试的文章来证明它：短路（主要在VB.Net和SQL Server中）

T-SQL is a declarative language hence it cannot control the algorithm used to get the results.. it just declares what results it needs. It is upto the query engine/optimizer to figure out the cost-effective plan. And in SQL Server, the optimizer uses "contradiction detection" which will never guarantee a left-to-right evaluation as you would assume in procedural languages.

T-SQL是一种声明性语言，因此无法控制用于获取结果的算法。它只是声明了它需要的结果。查询引擎/优化器可以找出具有成本效益的计划。在SQL Server中，优化器使用“矛盾检测”，它将永远不会保证从程序语言中假设的从左到右的评估。

For your example, did a quick test:
Created the scalar-valued UDF to invoke the Divide by zero error:

对于您的示例，进行了快速测试：创建标量值UDF以调用Divide by zero错误：

CREATE FUNCTION getMyFunction
( @MyValue INT )
RETURNS INT
AS
BEGIN
    RETURN (1/0)
END
GO

Running the below query did not give me a Divide by zero error encountered error.

运行以下查询没有给我一个Divide by zero错误遇到错误。

DECLARE @test INT
SET @test = 1
SET @test = ISNULL(@test, (dbo.getMyFunction(1)))
SELECT @test

Changing the SET to the below statement did give me the Divide by zero error encountered. error. (introduced a SELECT in ISNULL)

将SET更改为以下语句确实给了我遇到的Divide by zero错误。错误。（在ISNULL中引入了一个SELECT）

SET @test = ISNULL(@test, (SELECT dbo.getMyFunction(1)))

But with values instead of variables, it never gave me the error.

但是使用值而不是变量，它从来没有给我错误。

SELECT ISNULL(1, (dbo.getMyFunction(1)))
SELECT ISNULL(1, (SELECT dbo.getMyFunction(1)))

So unless you really figure out how the optimizer is evaluating these expressions for all permutations, it would be safe to not rely on the short-circuit capabilities of T-SQL.

因此，除非您真正弄清楚优化器如何为所有排列评估这些表达式，否则不依赖于T-SQL的短路功能是安全的。

#1