CODEWITHSUNDEEP
sqlsql-server
Karl Waugh
Karl Waugh

Reputation: 21

SQL behaviour when executing queries with conjunctions

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')
  1. 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?
  2. 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?

Upvotes: 2

Views: 162

Answers (3)

gbn
gbn

Reputation: 432200

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

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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.

Upvotes: 2

mellamokb
mellamokb

Reputation: 56769

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

Upvotes: 0

Related Questions