SQL Server - Query Short-Circuiting

Is the SQL WHERE clause short-circuit evaluated?

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by
parentheses, effective evaluation of expressions is generally
performed from left to right. However, it is
implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might
cause conditions to be raised or if the results of the expressions
can be determined without completely evaluating all parts of the
expression.

OR Operator Short-circuit in SQL Server

Within SQL, there is no requirement that an OR clause breaks early. In other words, it is up to the optimizer whether to check both conditions simutaneously. I am not an expert in the MSSQL optimizer, but I have seen instances where the optimizer has and has not short circuited an OR clause.

SQL Server - Query Short-Circuiting?

SQL Server does NOT short circuit where conditions.
it can't since it's a cost based system: How SQL Server short-circuits WHERE condition evaluation .

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

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

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Whether or not SQL Server short circuits your particular expression isn't relevant here, it will generate the correct result either way. In this case, short circuiting is merely an optimization.

And as for optimizations, it's up to how your index is built. These are (ideally) index seeks.

How to Short-Circuit SQL Where Clause

SQL Server does not do short-circuiting (nor should it).

If you need it to not try something under some circumstances, you need to force that in the way that you write your query.

For this query the easiest fix would be to use a CASE expression in your WHERE clause.

declare @queryWord as nvarchar(20) = 'asdas'

SELECT  * FROM TABLE_1 
WHERE TABLE_1.INIT_DATE = (CASE WHEN ISDATE(@queryWord) = 1 
                                THEN CONVERT(Date, @queryWord)
                           ELSE NULL  END)

Off-hand, CASE and query-nesting are the only two supported ways that I can think of to force an order of evaluation for dependent conditions in SQL.

How to achieve the equivalent of short-circuit evaluation in T-sql

You could evaluate in a CASE and provide a ranking value:

select
  mobile,
  case 
    when mobile = @mobile             then 1  
    when mobile like @mobile +'%'     then 2 
    when mobile like '%'+@mobile      then 3 
    when mobile like '%'+@mobile +'%' then 4
  end as [Rank]
from accounts where 
   mobile = @mobile or  
   mobile like @mobile +'%' or 
   mobile like '%'+@mobile or 
   mobile like '%'+@mobile +'%'
order by [Rank]

Does SQL Server short-circuit IF statements?

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.

---

Related Topics