Why do we set ANSI_NULLS ON before writing a script?

ANSI_NULLS property is used for comparing ANSI NULLS. By default it is ON in SQL Server.

As all of us aware that, we cannot compare a NULL with another NULL. i.e. We can not equal a NULL with another NULL or not equal to anither NULL.

When we set ANSI_NULLS is ON, the Equal to (=) and Not equal to (<> or !=) will not work in the WHERE condition and returns no records even if NULLs exist or does not exist in the data. We get 0 records since it cannot compare NULL with other NULL.

But when we set ANSI_NULLS is OFF, it does not follow the ANSI standards.

When we write a SELECT statement with where Col_Name = NULL, it fetches the records which are having NULLs in that Col_Name. It works in the similar way when we write a query with Col_Name <> NULL.

——————————————————————————-
USE [ADVENTUREWORKS2012]
GO
——————————————————————————-
— When ANSI_NULLS is ON
——————————————————————————-
SET ANSI_NULLS ON

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] IS NULL — Returns 10 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] = NULL — Returns 0 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] IS NOT NULL — Returns 10 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] <> NULL — Returns 10 Records
——————————————————————————-
— When ANSI_NULLS is OFF
——————————————————————————-
SET ANSI_NULLS OFF

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] IS NULL — Returns 10 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] = NULL — Returns 10 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] IS NOT NULL — Returns 10 Records

SELECT TOP 10 [AddressID], [AddressLine1],
[City], [PostalCode]
FROM [Person].[Address]
WHERE [AddressLine2] <> NULL — Returns 10 Records
——————————————————————————-

Note:

Even though it is on by default, it is a good practice to keep it ON before writing a query. This property might be ON in our Computer. But it might be off in others computer where they are executing this query.

We can change this property manually as below.

Query Tab — Query Options — ANSI — Check/Uncheck SET ANSI_NULLS Check box — Ok. 

Leave a Reply

Your email address will not be published. Required fields are marked *