Is MS-SQL AND/OR conditional (perform short-circuit evaluation)?

Question

I'm creating an stored procedure as this (simplified)

create procedure sp_mysp @p1 bit AS
if (@p1 = 1 AND EXISTS(select  * from mytable1))
BEGIN....END
ELSE IF (@p = 0 AMD EXISTS(select * from mytable2))
BEGIN....END

So the question is, will SQL always check for data in mytable1 or will it only check for data in mytable1 if @p1 equals to 1

(something like && in c#)

Nothing is guaranteed. You'd need to see what the execution plan does. — Martin Smith, Sep 09 '17 at 20:35
I'd be very surprised if the EXISTS executes if the first condition fails. Short-circuit boolean evaluation is a very common optimization, and I'd expect the SQL Server parser doesn't take advantage of it when it clearly can here. I'd rearrange your parentheses somewhat, however, to make it more clear: `if (@p = 1) and EXISTS()` — Ken White, Sep 09 '17 at 20:39
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Sep 09 '17 at 20:39

Martin Smith · Accepted Answer · 2017-09-09T21:04:48.407

There is no guarantee of this behaviour.

An example of short circuiting evaluation not happening with expr1 AND expr2 is

SET STATISTICS IO ON

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
PRINT 'Y'

The EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) is false (meaning the And-ed expression must be False) but the IO results show the second condition was still evaluated.

Table 'spt_values'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_monitor'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server can do this though. I see this in my test

SET STATISTICS IO ON

DECLARE @p1 BIT = NULL

IF ( @p1 = 1
     AND EXISTS(SELECT *
                FROM   master..spt_values) )
  PRINT '1'

ELSE IF ( @p1 = 0
     AND EXISTS(SELECT *
                FROM   master..spt_values) )
  PRINT '2'

The output is

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Showing spt_values was never accessed.

This is implemented by a pass through predicate condition in the execution plan. There is some information about those here.

If the passthru predicate evaluates to true, the join returns the row .... If the passthru predicate evaluates to false, the join proceeds normally

So should I use nested if statements if nothing is guaranteed or should I trust sql server to "do the right thing" — Mr Zach, Sep 09 '17 at 20:47
@MrZach - that's up to you. If it's critical that the second condition shouldn't execute use nested ifs. Otherwise it depends on your assessment as to how likely the short circuiting behavior you may observe today is to remain that way for all possible query optimisations and product updates. — Martin Smith, Sep 09 '17 at 20:59

score 2 · Answer 2 · answered Sep 09 '17 at 20:44

Short circuit is not guaranteed in SQL Server TSQL.

The database tries to optimize the query and may execute the expressions/operations in a different order than the observed in the query. You may check a blog entry from Microsoft, a set of experiments in SQL Server 2005 and another discussion on the OR behavior in SQL server.

Is MS-SQL AND/OR conditional (perform short-circuit evaluation)?

2 Answers2

Linked

Related