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
SQL Server does short-circuit sometimes, but the rules are complex, undocumented, and subject to change at any time.
The || OR operator is also a short-circuit operator. Since OR evaluates to true when one or both of its operands are true , short-circuit evaluation stops with the first true . The OR operator comes in a non-short-circuit version as well: | (this is a single vertical bar.)
The SQL AND, OR and NOT Operators The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With