Does SQL Server short-circuit IF statements?

Question

I am working on optimizing some heavily used stored procedures and ran across a scenario that raised a question that I couldn't find any answers for: when evaluating TSQL in a stored procedure, does SQL Server short-circuit the IF statement?

For example, assume a stored procedure has code similar to:

IF @condition1 = 1
OR EXISTS(SELECT 1 FROM table1 WHERE column1 = @value1)
...

In this scenario does SQL Server short-circuit the evaluation such that the EXISTS statement is never executed when the preceding clause evaluates to true?

If it never or only sometimes does, then we have some rewriting ahead of us.

Addle

Not guaranteed. Check the execution plan to see if it does in your case. – Superscription

Thanks, 'not guaranteed' is what I was looking for. The problem is that these stored procedures are executed on hundreds of customer DBs, so if the execution plan determines this, we cannot assume it will be evaluated the same on every customer's system and need to rewrite. – Addle

If you want a cast iron guarantee then separating out into multiple if statements will do that. When I looked at this before I found some examples of it not short circuiting. You could also look into using case statements. – Superscription

@MartinSmith: that is excellent info to have and I very much like the case statement approach. – Addle

This question seems to be asking if short circuiting occurs outside of a query. All answers and links I've seen are regarding the inside a query case. I would guess that in this particular case short circuiting does occur and is guaranteed but I have no idea. Is there anyone who can address the question of whether short circuiting occurs in a statement outside of a query (i.e. it's not in something like a Where clause)? – Antitrades

Answer 1

Even if it appears to work, it should not be relied upon. The CASE statement is the only thing that the documentation states as being short-circuiting, but even that isn't (or at least wasn't) always the case (hee hee). Here is one bug that was fortunately fixed as of SQL Server 2012 (see the comments).

In addition to the rabbit hole (an interesting one, for sure) of links in comments from the comment posted by @Martin on the question, you should also check out this article:

Understanding T-SQL Expression Short-Circuiting

and the discussion forum related to that article.

Marvin

Answer 2

The good news is that it seems to short-circuit. Here's a minimal example:

DECLARE @condition1 bit = 1

IF (@condition1 = 1) OR EXISTS(SELECT 1 FROM sys.objects)
    PRINT 'True'
ELSE
    PRINT 'False'

When @condition is set to 1, this is the execution plan: 0 rows scanned from sys.objects

when @condition is set to 0, it scanned the sys.objects table:

But there is no guarantee that this will be the case every time.

Stotinka