| Author |
Topic |
|
asafg
Starting Member
39 Posts |
 Posted - 2008-11-04 : 10:05:55
|
Hello, I want to know if the engine checks all the conditions of an "AND" phrase or it stops when the answer is false:EXAMPLE:IF((1=1) and (2=2) AND (3=3) AND (1=2) AND (4=4)), 1,0) Will it check the (4=4)?is it working the save for if queries and where statements?Thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 10:09:52
|
| http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
asafg
Starting Member
39 Posts |
Posted - 2008-11-04 : 10:48:24
|
| Thanks spirit1 for a great answer.My summary:1.SQL have execution plan of its own - so assumptions of what will be calculated first can't be made.2.All the statements will be evaluated3.The term is "short-circuiting"and the bottom line is SQL "Server does not do short-circuiting" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 11:05:13
|
1.,2.,3.: yes  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-04 : 14:40:05
|
quote:
Originally posted by asafg
Thanks spirit1 for a great answer.My summary:<snip>and the bottom line is SQL "Server does not do short-circuiting"
That is not true. SQL does have short-circuiting, but being able to control the order of expressions evaluated to control it is not guaranteed. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 14:42:02
|
| in the where condition no.in the IF block yes._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-04 : 14:48:41
|
quote:
Originally posted by spirit1
in the where condition no.in the IF block yes.[/b]
What do you get if you execute this?select 'short circuit' where 1=0 and 1/0 = 0 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 14:55:53
|
| read the post at my upper link please._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-04 : 15:01:50
|
quote:
Originally posted by spirit1
read the post at my upper link please._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
I have. And I read Jeff's article and the links in Jeff's article.As a practical matter, I agree that one cannot use/rely on short-circuiting. As I said above, SQL Server may or may not evaluate expressions from left to right like a procedural language. But, to say it does not exist is erroneous. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 15:11:40
|
| when dealing with data it does not exists. and that's basically all that matters, no?but ok.. if you want to get really technical it exists but it's no use to you so it might as well not exist._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
|
AND execution in T-sql