Null Sql comparison with = and <> when SET ANSI_NULLS ON

ANSI_NULLS should be set to ON in all the null comparison queries in latest versions of SQL servers when using = (equal to) and <> (not equal to).
If you have written a null comparison in sql like select * from sometable where column=null then query wont return any data in sql server unless it is changed with �IS NULL� statement. Same is for the comparison <> null. All the <> null comparison should change to column IS NOT NULL.

This change is enforced in SQL server version > 2005 is to make sure that SQL queries are ISO compliant.

More details about this can be found at: http://technet.microsoft.com/en-us/library/ms188048.aspx


I have rewritten the example with a temporary table so that it is easy for you to understand the ANSI_NULLS settings in SQL stored procedures or queries.

Code:

DECLARE  @tblvar TABLE(a INT NULL);

INSERT INTO @tblvar values (NULL);
INSERT INTO @tblvar values (10);
INSERT INTO @tblvar values (20);
INSERT INTO @tblvar values (30);
INSERT INTO @tblvar values (0);

DECLARE @varname int;
SET @varname = NULL;


SET ANSI_NULLS OFF;

PRINT 'ANSI_NULLS OFF';

-- get all the rows with null values in them
SELECT * FROM @tblvar WHERE a = @varname;

-- not null values
SELECT * FROM @tblvar WHERE a <> @varname;

SELECT * FROM @tblvar WHERE a IS NULL


SET ANSI_NULLS ON;

PRINT 'ANSI_NULLS ON';

-- get all the rows with null values in them
SELECT * FROM @tblvar WHERE a = @varname;

-- not null values
SELECT * FROM @tblvar WHERE a <> @varname;

SELECT * FROM @tblvar WHERE a IS NULL

Please post your comments here if you have any.


Thanks