logical and, or
-
Hi all a question on the order that 'and/or' logic is read in T-SQL for IF statements and when using IN logic.
In C and C++ logical and/or statements are read left to right and the read will terminate on the first true condition. Thus faster execution can be obtained if the most likely statement is placed first in a complex IF statement.
In Visual basic the entire statement is read before the function returns a result. Thus there is no advantage in trying to arrange the order of the statement.
Does anyone know which system T-SQL uses?
The time savings may be quite small but hey a nano second here and a nano second there and soon you will save real time.
Its always better to take an extra second and do it right the first time. Thanks
Mike
-
The fact that you can check for zero and still get an error (unless you use a case statement) tells me that it uses the VB style of evaluating everything first.
Love to be wrong though... and I do tend to write my stuff in the hope that one day it will do it the better way.
Sorry.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Hi mike, Does anyone know which system T-SQL uses?
You can try the following experiment to test which type it follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_true]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_true]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fn_true(@param varchar(250) = 'dir_out.txt' )
RETURNS int AS
BEGIN
DECLARE @cmd sysname, @var sysname
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > ' + @param
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
RETURN 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if (dbo.fn_true('1_or_out.txt') = 1) or (dbo.fn_true('2_or_out.txt') = 1)
begin
print 'hello; or world'
end
GO
if (dbo.fn_true('1_and_out.txt') = 1) and (dbo.fn_true('2_and_out.txt') = 1)
begin
print 'hello; and world'
end
GO
--the file generated can be found under the
--windows directory C:\Windows\System32
tell me how it goes
. hope this helps -
Bersileus thanks for the test. Very interesting way of checking, now why did I not think of that. And the results are ta ta T-SQL uses the C style of reading logical statements.
Thanks
Mike
-
Rob could you give me an example of checking for zero where you generate an error.
Thanks Mike
-
Rob the Truth table in T-SQL when using the AND operator is not the same as the Truth table C uses. Could this be the cause of your error when checking for 0?
Truth table SQL
True AND False = unknown
False AND False = False
Unknown AND False = Unknown
Truth Table C
True AND True = True
True AND False = False
False AND True = False
False AND False = True
Thanks Mike
-
Eek.. I'm wrong.
select *
from sysobjects
where 1/parent_obj = 1
and parent_obj > 0
works just fine. I guess it comes down to the SQL optimiser. It figures that parent_obj > 0 is easier to check than dividing it by zero.
On the other hand:
select *
from sysobjects
where 1/parent_obj = 1
and (parent_obj * 1) > 0
gives an error, but
select *
from sysobjects
where (parent_obj * 1) > 0
and 1/parent_obj = 1
doesn't.
So... it really comes down to what the optimiser reckons is the best way of doing something.
I'm not sure what you mean by your truth tables. Your C one looks like "Not XOr" to me.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
My point about using the case statement to avoid checking is that:
select case when field = 0 then 0 else 1.0/field end
doesn't try to calculate "1.0/field" if "field=0" is true.
And typically, I find that if I want to be really sure that half the test doesn't ever get calculated unless appropriate, I use a case statement. Not so efficient, but you're not going to get caught out by a bad execution plan.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Rob "XOR" returns true if one and only one of the two conditions is true. "NOT XOR" returns true if one and only on of the conditions is false. When using XOR both statements have to be read to check to see if both are true.
true XOR true returns false--both sides read
False XOR True returns true--both sides read
True XOR False returns true--both sides read
FALSE XOR False returns False--both sides read
"OR" returns true if one or both of the statements is true.
True or true returns true--only oneside read
True or False returns true --one side read
False or true returns true-- both sides read
False or False returns False --both sides read
IN C "AND" will return TRUE when both conditions are TRUE or both conditions are FALSE.
When using the "AND" operator and T-SQL encounters the condition where only one of the conditions being compared is true it returns UNKNOWN rather than FALSE. It only returns TRUE when both statements. are true And returns False when both statements are FALSE. FALSE.{Edited I removed the statement that SQL never returned false Thanks Rob for showing me the error of my }
Thanks
Mike
I'll need some time to think about your examples.
-
Rob forgot the NOT XOR logic sorry.
NOT XOR returns true when one and only one of the conditions is false.NOT true XOR true returns false--read both
NOT true XOR False returns True --read both
NOT False XOR False returns false --read both
NOT false XOR true returns true --read both
Mike
-
Rob
Since SQL reads logical statements from left to right and stops the reading when it hits the first not true condition preventing a divide by zero error is easy.
Select whatever From somewhere
WHERE divisor <> 0 AND NOT IS NULL Divisor AND dividend / divisor = some number
Using <> 0 allows the use of positive and negative numbers as the divisor. If divisor = 0 then the second condition is never read and you will not get an error. If divisor is null then the third condition will never be read. The only time you reach the third condition is when divisor <> 0 and divisor is not null.
Mike
p.s. notice that I used "is null" and not "isnull". Two different functions
-
No, that's not right. The optimiser works out which way it thinks will be best. That may not be left-to-right at all. But in the example you give, the optimiser is more likely to test 'divisor 0' before the others.
And... in SQL, AND is only true if both A and B are true.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
You are right about SQL returning False when both conditions are False. Where can I find information on the optimizer changing the order of logical statements.
Mike
-
Tough one. I'm not sure myself. I just know that whenever I have a scenario that could cause an error for certain rows if it's calculated in the 'wrong' order, I bury that condition in something like a case statement.
I find it's particularly an issue with dates. Particularly converting to a date after using isdate. I tend to use something like:
and case when isdate(field) then convert(datetime,field) else '1-jan-1900' end > @datefilter
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
This article from http://www.sql-server-performance.com/transact_sql.asp states that SQL evaluates AND operators from left to right as they were written. Hey it came off the internet it must be true. Do You have an conflicting ref.
If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. This assumes that no parenthesis have been used to change the order of execution. Because of this, you may want to consider one of the following when using AND:
· Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.
· If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply