SQL behaviour when executing queries with conjunctions

Question

If I have a SQL query that is performing an existence check against three tables:

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
    AND EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    AND EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')

Does SQL Server support 'early exit' for conditional statements, such that if the initial exists check against [Table1] returns false, the remaining two exists checks are not executed?
Assuming Microsoft SQL Server as the backend, what locking behaviour would I expect to see on the three referenced tables, again assuming that the initial exists check against Table1 will return false?

Some basic testing using functions instead of actual queries would suggest that 'early exit' is supported, but lock analysis during query execution also suggests that a lock is acquired on all three tables which contradicts the 'early exit' findings.

Does SQL Server acquire a lock on all tables in a query, just in case it needs them later on?

SQL statements are supposed to be atomic. By combining three seperate queries into a single one as you have, you force SQL server to lock the involved tables, so it can retrieve consistent results. Otherwise some of those keys you're checking could pop in/out of existence in one table while the actual query is executing on another. — Marc B, May 16 '11 at 19:09

score 2 · Answer 1 · answered May 16 '11 at 19:11

2

SQL Server does do short-circuit evaluation, but you cannot control the order in which it chooses to evaluate the clauses, unless you do so via a CASE statement.

answered May 16 '11 at 19:11

D'Arcy Rittich

167,292
40
290
283

Actually, CASE has been demonstrated to not be predictable. Will find demo script/article – gbn May 16 '11 at 19:13
@gbn thx, would be appreciated. My info from: http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx – D'Arcy Rittich May 16 '11 at 19:14
OK found it, turns out to be a SQL Server bug (Mar 2011 update) from http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/. There is stuff in the comments to your link from one of the MS SQL Server teams too http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596401.aspx – gbn May 16 '11 at 19:16
np. Updated my answer to reflect new information. You are correct so +1 – gbn May 16 '11 at 19:24

gbn · Answer 2 · 2011-05-16T19:23:37.787

Edit: there is a bug in SQL Server apparently that kiboshes short circuiting occasionally. See comments on RedFilter's answer. ~~If you want short circuiting, use nested IFs.~~

Generally SQL is declarative, not procedural, so you can never assume any expression or query will be evaluated in the order its written. Edit: except for CASE...

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
BEGIN
    IF EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    BEGIN
        IF EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')
        BEGIN

This would also change how locks are applied: now you'll have separate locks for each query instead of locks for all three tables for the duration of the first AND expression

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

SQL Server does not short-curcuit. All parts of the statement have to be evaluated and all relevant locks taken while executing the query.

edited May 23 '17 at 12:26

Community

1
1

answered May 16 '11 at 19:10

mellamokb

56,094
12
110
136

SQL behaviour when executing queries with conjunctions

3 Answers3