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

Answer 1

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

Answer 2

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.