CODEWITHSUNDEEP
sql-serversql-server-2008-r2operator-precedence
Nick Vaccaro
Nick Vaccaro

Reputation: 5504

WHERE - Order of Operations

A comment in this question:

Do not rely on short-circuiting ... Those clauses could be evaluated in any order SQL Server sees fit.

As it relates to this answer:

SELECT * FROM TABLE WHERE (@vari is null or col = @vari)
  1. Is this correct?
  2. Is there a good test setup, perhaps in SQL fiddle, that can reproduce this phenomenon? (used that term pretty loosely)
  3. Does this only occur in WHERE clauses? Or perhaps only with AND/OR operators?

I ask this because I haven't found anything online that explicitly states this.

Upvotes: 1

Views: 260

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Read here

http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors

SQLFiddle

Sample setup

create table albert(a int NOT NULL,
                        b varchar(23) NOT NULL)
create table stina (a int NOT NULL)
go
insert albert (a, b)
     values (1, '99'),
             (2, 'Gurka'),
             (3, '89')
insert stina (a) values(1), (3), (9), (12)
go

SELECT a.a, a.b + 100
FROM albert a
JOIN stina s ON s.a = a.a

Upvotes: 1

Related Questions