Solved

Question on TSQL

Posted on 2014-12-16
13
71 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 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 49

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
While in ##Table - Help 4 22
union query column need default text 2 20
SQL Database Restore 2008 R2 1 27
T-SQL: Please describe what a page split is 5 31
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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