Sql Server的ISNULL()函数是惰性的还是短路的? [英] Is Sql Server's ISNULL() function lazy/short-circuited?

问题描述

TIs ISNULL()是惰性函数吗?

TIs ISNULL() a lazy function?

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

That is, if i code something like the following:

SELECT ISNULL(MYFIELD, getMyFunction()) FROM MYTABLE

它将始终评估getMyFunction()还是仅在MYFIELD实际上为空的情况下评估它?

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

推荐答案

它是认为最有效的方法.

It's whichever it thinks will work best.

现在它在功能上是惰性的，这很重要.例如.如果col1是varchar，并且在col2为空时将始终包含数字，则

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

isnull(col2, cast(col1 as int))

会工作的.

但是，未指定是在col2不为null时还是在空检查之前或同时尝试强制转换并吃掉错误，或者是否仅在col2为null时才尝试强制转换空.

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.

至少，我们希望它在任何情况下都能获得col1，因为单次扫描获得2个值的表的速度将快于两次扫描各自获得1个值的速度.

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.

相同的SQL命令可以以非常不同的方式执行，因为我们给出的指令根据对表的索引和统计信息的了解而转换为较低级的操作.

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.

基于这个原因，就性能而言，答案是看起来好像是个好主意，否则就不是".

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.

Mikael Eriksson的回答表明，有些情况下由于不懒惰而确实可能会出错.我将在性能影响方面坚持我的回答，但就至少在某些情况下的正确性影响而言，他至关重要.

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.

这篇关于Sql Server的ISNULL()函数是惰性的还是短路的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！