OR Operator Short-circuit in SQL Server

Question

I want to consult SQL Server OR short-circuit

Code:

DECLARE @tempTable table
    (
        id int
    )
INSERT @tempTable(id) values(1)
      
DECLARE @id varchar(10)
SET @id = 'x'
SELECT * FROM @tempTable WHERE 1=1 OR id = @id --successfully
SELECT * FROM @tempTable WHERE @id = 'x' OR id = @id --Exception not Convert 'x' to int

Why? 1=1 and @id='x' are true.

SQL Server OR operator : whether the short-circuit function？

THANKS

Paella

There is no guarantee whatsoever on how and which parts of an OR condition are evaluated first (or at all). T-SQL is NOT like C# in that way. You cannot rely on boolean short-circuiting. – Ethe

Why? 1=1 and @id='x' is true - it is or actually, not and. – Vahe

Here, in this INSERT @tempTable(id) values(1),INTO is missing. – Myocardiograph

OR short circuited is My doubts but my office codeing sql is((@id is NULL OR id = id) AND (@name IS NULL OR name=@name)) so I would like to know whether the short-circuit Because This decision process efficiency To do so, just reuse the query plan – Paella

Answer 1

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.

Labe

OR short circuited is My doubts but my office codeing sql is((@id is NULL OR id = id) AND (@name IS NULL OR name=@name)) so I would like to know whether the short-circuit Because This decision process efficiency To do so, just reuse the query plan – Paella

I have just experienced a confusing case that demonstrates this. I was trying to parse JSON out of a varchar field, and I included a WHERE clause to include only the rows with valid JSON text. So far so good; running the SELECT works. Using that query in a WITH and joining it to another table works. But any attempt to write a WHERE condition dependent upon that column, even several queries later joining on the WITH table, results in JSON parsing failures. The optimizer is magical indeed. – Arnulfo

Answer 2

Just stumbled over this question, and had already found this blog-entry: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

The SQL server is free to optimize a query anywhere it sees fit, so in the example given in the blog post, you cannot rely on short-circuiting.

However, a CASE is apparently documented to evaluate in the written order - check the comments of that blog post.

Trumpeter

Answer 3

It is but obvious that MS Sql server supports Short circuit theory, to improve the performance by avoiding unnecessary checking,

Supporting Example:

SELECT 'TEST'
WHERE 1 = 'A'

SELECT 'TEST'
WHERE 1 = 1 OR 1 = 'A'

Here, the first example would result into error 'Conversion failed when converting the varchar value 'A' to data type int.'

While the second runs easily as the condition 1 = 1 evaluated to TRUE and thus the second condition doesn't ran at all.

Further more

SELECT 'TEST'
WHERE 1 = 0 OR 1 = 'A'

here the first condition would evaluate to false and hence the DBMS would go for the second condition and again you will get the error of conversion as in above example.

NOTE: I WROTE THE ERRONEOUS CONDITION JUST TO REALIZE WEATHER THE CONDITION IS EXECUTED OR SHORT-CIRCUITED IF QUERY RESULTS IN ERROR MEANS THE CONDITION EXECUTED, SHORT-CIRCUITED OTHERWISE.

SIMPLE EXPLANATION

Consider,

WHERE 1 = 1 OR 2 = 2

as the first condition is getting evaluated to TRUE, its meaningless to evaluate the second condition because its evaluation in whatever value would not affect the result at all, so its good opportunity for Sql Server to save Query Execution time by skipping unnecessary condition checking or evaluation.

in case of "OR" if first condition is evaluated to TRUE the entire chain connected by "OR" would considered as evaluated to true without evaluating others.

condition1 OR condition2 OR ..... OR conditionN

if the condition1 is evaluated to true, rest all of the conditions till conditionN would be skipped. In generalized words at determination of first TRUE, all other conditions linked by OR would be skipped.

Consider the second condition

WHERE 1 = 0 AND 1 = 1

as the first condition is getting evalutated to FALSE its meaningless to evaluate the second condition because its evaluation in whatever value would not affect the result at all, so again its good opportunity for Sql Server to save Query Execution time by skipping unnecessary condition checking or evaluation.

in case of "AND" if first condition is evaluated to FALSE the entire chain connected with the "AND" would considered as evaluated to FALSE without evaluating others.

condition1 AND condition2 AND ..... conditionN

if the condition1 is evaluated to FALSE, rest all of the conditions till conditionN would be skipped. In generalized words at determination of first FALSE, all other conditions linked by AND would be skipped.

THEREFOR, A WISE PROGRAMMER SHOULD ALWAYS PROGRAM THE CHAIN OF CONDITIONS IN SUCH A WAY THAT, LESS EXPENSIVE OR MOST ELIMINATING CONDITION GETS EVALUATED FIRST, OR ARRANGE THE CONDITION IN SUCH A WAY THAT CAN TAKE MAXIMUM BENEFIT OF SHORT CIRCUIT

Thanks and Regards,

Rk_Hirpara

Turquoise

Downvote reason: always test things on a real server, with a reasonable data set. For example, try this more realistic where clause against a character field - where isnumeric(fieldname) = 1 AND convert(decimal, fieldname) <= 0 - you will find that it suffers a conversion error on rows where isnumeric = 0, even though it technically should not need to evaluate the second condition on such rows. – Sanches

I hardly ever downvote, but this answer is apparently just wrong. – Jurkoic