AXISHK
asked on
Question on TSQL
for the where condition in TSQL;
where
conditionA and conditionB or conditionC or condition D and condition E
In this case, how does the TSQL process this condition ?
Tks
where
conditionA and conditionB or conditionC or condition D and condition E
In this case, how does the TSQL process this condition ?
Tks
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Just as an extra note; in situations like yours it would be common practice to bracket the clauses in order to make it clear to anybody else looking at the code what your intention was.
I would always go for a solution with brackets. It's better for read and also if in the future you want to add or change something in the logic it's more easy to move a block inside the brackets than loosing time to remember again all the logic.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@Scott: What do you say to this statement:
select 'c'
where
1/0 = 2 and 'a' = 'b'
----
(0 row(s) affected)
@chaau:
I say it's absolute proof that SQL does indeed short-circuit when possible, as I stated above.
Again, where did you get the info that SQL does not s-c?
I say it's absolute proof that SQL does indeed short-circuit when possible, as I stated above.
Again, where did you get the info that SQL does not s-c?
@Scott: The 1/0 = 2 and 'a' = 'b' proves that it is not a short circuit but what they claim is a cost-based validation. SQL Server chooses what is in its opinion is "less cost" but not left to right validation as in C++ for example. You can't really say what SQL Server chooses as the first statement to validate.
Take for example this statement:
Take for example this statement:
select 1 where
CEILING(1.22) = 2 and 1/0 = 2
-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
OK, I must admit, it is a short-circuiting, but the order of the validation is unpredictable
@chaau:
Yes, SQL may change the order of evaluation of expressions. But it can short-circuit as well, and does so. You don't really seem to understand what a short-circuit is if you can try to claim that ignoring the condition after an "and" is not a short-circuit.
Yes, SQL may change the order of evaluation of expressions. But it can short-circuit as well, and does so. You don't really seem to understand what a short-circuit is if you can try to claim that ignoring the condition after an "and" is not a short-circuit.
As I admitted before, it does short-circuit, however for a person that comes from a C/C++/Java development world it can be a bit confusing. Because of the unpredictable nature of its validation you can't really use a left-to-right logic, like
select textvalue from aTable WHERE ISNUMERIC(textvalue) = 1 and convert(int, textvalue) = 1
I'm stunned that C++ and/or Java don't optimize the order of comparisons as well. If there are multiple conditions, it makes great sense to do that cost the least to do first.
In particular, if you have a condition like this:
(where exists(...) and exists(....) and exists(...)) or col1 = 5
It will be vastly less overhead to check the single column comparison first before having to do all perhaps three exists checks. If the second condition is true, the first set of conditions can be short-circuited.
In particular, if you have a condition like this:
(where exists(...) and exists(....) and exists(...)) or col1 = 5
It will be vastly less overhead to check the single column comparison first before having to do all perhaps three exists checks. If the second condition is true, the first set of conditions can be short-circuited.
ASKER
Tks
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
(conditionA and conditionB)
or conditionC
or (condition D and condition E)