Is the Sql Server ISNULL () function lazy / short-circuited?

Question

TIs ISNULL() lazy function?

That is, if I code something like the following:

SELECT ISNULL(MYFIELD, getMyFunction()) FROM MYTABLE

will it always evaluate getMyFunction() or will it only evaluate it when MYFIELD is actually null?

Answer 1

Depending on what he thinks will work best.

Now he is functionally lazy, which is important. For example. if col1 is a varchar that will always contain a number, when col2 is null, then

 isnull(col2, cast(col1 as int)) 

Will work.

However, he did not indicate whether he would try listing before or simultaneously with a null check and there is an error if col2 not null, or if he will try to perform only listing if col2 null.

At least we expect it to get col1 anyway, because one scan of a table receiving 2 values ​​will be faster than two scans receiving one each.

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

For this reason, in terms of performance, the answer is "when it seems that it would be a good idea, otherwise it is not."

In terms of observable behavior, he is lazy.

Edit: Michael Ericsson's answer shows that there are cases that can really make a mistake due to the fact that they are not lazy. I will adhere to my answer here in terms of impact on performance, but it is vital in terms of correct impact, at least in some cases.

Answer 2

It works great

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

But introducing an aggregate will lead to a failure and proof that the second argument can be evaluated before the first, sometimes.

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

Answer 3

Judging by different behavior

 SELECT ISNULL(1, 1/0) SELECT ISNULL(NULL, 1/0) 

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

Answer 4

This "lazy" function you are talking about is actually called a "short circuit"
And this does NOT always work, especially if you have udf in the ISNULL expression.
Check out this article where tests were tested:
Short circuit (mainly in VB.Net and SQL Server)

T-SQL is a declarative language, so it cannot control the algorithm used to get the results. He simply announces what results he needs. The query optimizer depends on an economical plan. And in SQL Server, the optimizer uses "inconsistency detection", which never guarantees a left-to-right evaluation, as you would expect in procedural languages.

---

An example is a quick test:
Created a scalar-digit UDF to cause Division by zero error:

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

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

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

Changing SET in the description below gave me a Divide by zero error encountered. . (introduced SELECT in ISNULL )

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

But with values ​​instead of variables, he never gave me an error.

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

Therefore, unless you really find out how the optimizer evaluates these expressions for all permutations, it would be safe not to rely on the possibility of a T-SQL short circuit.