September 9, 2004 at 9:01 pm
I just got bitten by this and was surprised to learn this.
Assume SET ANSI_NULLS OFF and column COL1 contains some values (0,1) and some NULL values for both of the following queries.
DECLARE @varname int
SELECT @varname = 1
SELECT *
FROM table
WHERE COL! <> @varname
...will return nulls along with all values that are not 1. This makes sense. However, if you use a hard-coded value in place of the variable, the results are different in that nulls are not returned, even though the hard-coded value is exactly the same as the variable's value!
SELECT *
FROM t1
WHERE a <> 1
...will return all value not equal to 1 but will exclude the nulls. WHY???? When did I have to be concerned about using a hard-coded value in place of a variable? Is this a bug or is this the way it works and is documented somewhere?
Here is a quick script so you can test yourself to see the effect. Can someone explain what is going on here? I was buidling some apps in a script and using some hard-coded values only to learn that the nulls were being excluded and I did not think they should be. After replacing the the hard-coded value with a variable, it worked as expected.
-- Create table t1 and insert values.
CREATE TABLE t1 (a int null)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO
-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
-- this will return the null value row along with the 0 value row as expected.
DECLARE @varname int
SELECT @varname = 1
SELECT *
FROM t1
WHERE a <> @varname
GO
-- this will exclude the null value row when implicit value is used rather than a variable.
SELECT *
FROM t1
WHERE a <> 1
GO
-- Drop table t1.
DROP TABLE t1
GO
TIA!
Don
September 10, 2004 at 6:42 am
Actually appears to be by design. Check the comparison chart on this page http://support.microsoft.com/default.aspx?scid=kb;EN-US;214601 which refers to the behavior in 6.x and 7. Apparently still applies to 2000.
Look at the SQL Server 7.0 with <> comparison and ANSI_NULLS OFF chart and you will see constant and variable are evaluated opposite of each other with regards to a null.
September 10, 2004 at 8:02 am
Very much appreciated. You found the exact page where MS documents this behavior. And sure enough, the <> evaluates a non-null constant as opposite from a non-null variable. So when a developer builds a simple SQL query like:
where col1 <> 'ABC', it simply will not return the expected rows. Instead, you have to write it as...
where (col1 <> 'ABC' or col1 is null) ...just as Microsoft indicates on this very page.
The only other choice would be construct the query like:
Declare @var1 varchar(10)
Set @var1 = 'ABC'
..
where col1 <> @var1 ..which will return expected results but is a lot harder to construct programmatically.
Oh well...just when you think you have things figured out with ANSI_NULLS, you find yet another anomaly.
Thanks again,
Don
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply