Solved

Question on TSQL

Posted on 2014-12-16
13
67 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
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 300 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
 
LVL 46

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:ScottPletcher
ScottPletcher earned 200 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 24

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:ScottPletcher
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 24

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 24

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:ScottPletcher
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 24

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:ScottPletcher
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now