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

Advertisements