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

Question

TIs ISNULL() a 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 in the case where MYFIELD is actually null?

Glabella

You could add a PRINT statement to your function and find out for yourself. – Pettifog

@JarrettMeyer I used that technique to learn programming in my childhood time, but it doesn't apply nowadays because you don't know what is implementation detail and what is documented behavior. Learning stuff is getting hard :( just saying... – Hypaethral

@JarrettMeyer, PRINT is not acceptable in a udf. – Solley

Answer 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

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.

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.

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.

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.

Allomerism

You give a good explanation of the fact that it may or may not be lazy depending on the server's optimization decisions. Thanks. – Glabella

Welcome. Though I'll add a caveat. – Allomerism

Answer 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))

Watereddown

Answer 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.

Calculable

This not entirely reliable. Subqueries might cause trouble due to different rewrite rules coming into play. – Jasmine

@usr, they won't cause the error condition to happen if the first field is always null. That isn't to say that the server isn't calculating 1/0 and then eating the error when it sees that the expression isn't used - it's free to do whatever it wants with the same outside behaviour. – Allomerism

@JonHanna, yes my comment was directed at potential performance issues. – Jasmine

@Jasmine of course it cuts both ways. With IsNull(colA, colB) it would be horrible if it didn't retrieve colB just because it might not be needed, and so ended up doing 2 scans. – Allomerism

Answer 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)

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.

---

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

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.

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 @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.

Solley

I think this answer is the nail in the coffin. Now I definitely cannot rely on it being short-circuited. – Giorgi