Tags
From BOL:
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.
Syntax
SET ANSI_NULLS {ON | OFF}
Now I will try to demonstrate with a simple example. In this example I will use AdventureWorksLT2008 Database and SalesLT.Product table.
Lets see the content of the table first:
SELECT * FROM AdventureWorksLT2008.SalesLT.Product
I could only select few columns and rows for this picture and this should be sufficient for the today’s demonstration. Please look at the highlighted column “Weight”- It has both NULL and non-NULL values.
I will create a Stored Procedure which will select all the rows WHERE Weight <> NULL
— Setting the ANSI_NULLS OFF
SET ANSI_NULLS OFF
GO
CREATE PROC Ansi_Null_Test_OFF
AS
BEGIN
SELECT * FROM AdventureWorksLT2008.SalesLT.Product
WHERE [Weight]<>NULL
END
Lets execute the above Store Procedure and see the resultset:
EXEC Ansi_Null_Test_OFF
As we can see it will return all the rows from “SalesLT.Product” table whose Weight<>null.
Now lets create the same Stored Procedure we created above with ANSI_NULLS ON.
— Setting the ANSI_NULLS ON
SET ANSI_NULLS ON
GO
CREATE PROC Ansi_Null_Test_On
AS
BEGIN
SELECT * FROM SalesLT.Product
WHERE [WEIGHT]<>NULL
END
— Executing the above Stored Procedure
EXEC Ansi_Null_Test_On
So we can see that it is not returning any rows because when SET ANSI_NULLS is ON then any comparison against NULL values evaluates to FALSE.
I hope I could explain the concept in clear and concise way. Let me know if you have any other question regarding this topic.
Thanks,
Subhro Saha