?
Solved

Question on TSQL

Posted on 2014-12-16
13
Medium Priority
?
75 Views
Last Modified: 2014-12-17
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
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1200 total points
ID: 40504043
According to the operator precedence rule.
The resulting clause is an equivalent to:
where
   (conditionA and conditionB) or conditionC or (condition D and condition E)

Open in new window

Please note that SQL Server does not have a short-circuiting, meaning that in the clause like this:
where 
    'a' = 'b' and 1/0 = 2

Open in new window

there will be an exception due to division by zero
0
 

Author Comment

by:AXISHK
ID: 40504260
So, either one of these 3 condition, correct ?

     (conditionA and conditionB)
or conditionC
or (condition D and condition E)
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40504320
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40504657
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.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 40504927
>> Please note that SQL Server does not have a short-circuiting, meaning that in the clause like this: <<

SQL Server does indeed short-circuit when it "believes" it can.  Not sure where you got your claim from.

Indeed, if we run your exact statement:
select 'c'
where
    'a' = 'b' and 1/0 = 2
we get an empty result set, not a "divide by zero" error.
0
 
LVL 25

Expert Comment

by:chaau
ID: 40505728
@Scott: What do you say to this statement:
select 'c'
where
    1/0 = 2 and 'a' = 'b'

----

(0 row(s) affected)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40505838
@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?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40505879
@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:
select 1 where 
CEILING(1.22) = 2 and 1/0 = 2

-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Open in new window

In this case SQL Server chose to validate the second condition first.
0
 
LVL 25

Expert Comment

by:chaau
ID: 40505884
OK, I must admit, it is a short-circuiting, but the order of the validation is unpredictable
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40505898
@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.
0
 
LVL 25

Expert Comment

by:chaau
ID: 40505923
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

Open in new window

There is of course a workaround to use a CASE statement which I actually use from time to time
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40505935
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.
0
 

Author Closing Comment

by:AXISHK
ID: 40506069
Tks
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question