logical and, or

Question

Post reply

logical and, or

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Rob Farley SSCrazy Points: 2739 More actions · Answer 1

If that were true, this would fail:

select *

from sysobjects

where 1/parent_obj = 1

and parent_obj > 0

...because it would evaluate the 1/0 before it had eliminated them.

I'm jumping offline now. Back in about 12 hours.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 2

Rob you are right and just when I was feeling so good about having one of the experts agree with me. While you are off playing in the sunshine I will attempt to get a little more information. But and in my case that is no small thing it looks like SQL does not follow the left to right read pattern. Making it easy to write the code but it also eliminates the possibability of picking up a nano second or two through rearrainging your conditoins.

Mike

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

I think it generally goes left-to-right. But it tries to look for conditions that are easier to check first. You should always try to avoid letting it go anywhere near any conditions that could cause an error.

And there are much better things to do to save a few nanoseconds. For example... if you're looking at a datetime field, use:

and field1 > dateadd(month,-1,getdate())

instead of:

and dateadd(month,1,field1) > getdate()

Using stuff like this, you can save far more than nanoseconds...

Good luck - I'm sure it will work out okay for you.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

AFAIK, Transact SQL is parsed left from right, starting from the innermost parenthesis. (haven't verified it though )

About comparing truth tables... SQL is a tri-state language. An expression can evaluate to either true, false or unknown (null). This is special from all other languages, and requires a whole different mindset. When you consider a SQL statement, it isn't applied in a sequential way, but it rather 'happens all at once' - at least in a logical sense.

While there may be cases when you have extremely large or complex SQL statements, that you may be able to influence the parse and compile times, the place one really wants to focus on (and where the true benefits lie) is to get the query to run as efficient as possible. (ie retieve or modify the qualifying rows)

Though the original question is still a valid one - it's always good to know how a language works. It may sometimes help you to optimize, but I believe that most of the times it will prevent one from writing something that doesn't do what one intended. SQL is very non-prejudice and will give you an answer to exactly what you have asked, not what you thought you asked

/Kenneth

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 5

Rob, Kenneth many thanks to both of you for your input.

Kenneth "SQL is very non-prejudice and will give you an answer to exactly what you have asked, not what you thought you asked ' mean that SQL has a very Feminine outlook. I once had a wife who always insisted that she was answering the question I asked, not what I thought I had asked. Its hard to argue with that type of logic.

Thanks again to both of you especially Rob.

Mike

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Heh. I use to think it's the other way around - you ask them (the girls) something and you get an answer. When you later apply that answer they get upset because that's 'not what they ment'!

I try to defend by explaining that mind reading isn't a skill yet developed by us guys, though them girls seem to be sure that it is. It's hard to argue about that type of logic too.

/Kenneth