Solved

Question on TSQL

Posted on 2014-12-16
13
73 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 50

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 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