OR Operator Short-circuit in SQL Server
Solution 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.
Solution 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.
NotTwoWayStreet
Updated on June 13, 2022Comments
-
NotTwoWayStreet about 2 years
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'
aretrue
.SQL Server OR operator : whether the short-circuit function?
THANKS
-
NotTwoWayStreet almost 12 yearsOR 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
-
Roland Dercsényi over 8 yearsDownvote 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.
-
Culme about 6 yearsI hardly ever downvote, but this answer is apparently just wrong.