SET ANSI_NULLS (ON | OFF) in SQL Server

The ANSI_NULLS database SET option controls the behavior of Equal (=) and comparison operators (!=) in queries while dealing with NULL records. It is an ISO standard to decide for a query action to conclude how SQL Server should handle the comparison with NULLs.

Most of us must have seen the ANSI_NULLS ( ON | OFF) setting used at the start of a stored procedure, function, or other database objects.

Important note from SQL Server Books online:

In a future version of SQL Server, ANSI_NULLS will always be ON, and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Syntax

SET ANSI_NULLS { ON | OFF }  

SET ANSI_NULLS ON

When the ANSI_NULL database option is set to ON, then a comparison with NULL records yields UNKNOWN. Hence, no rows are returned. If you compare anything with NULL, it will result as UNKNOWN, and also the NULL = NULL comparison will be considered as UNKNOWN. You cannot compare NULL against anything. This is an ISO standard when dealing with NULL records.

SET ANSI_NULLS OFF

When the ANSI_NULLS database option is set to OFF, then a comparison with NULL records returns rows as a comparison that evaluate to TRUE instead of UNKNOWN. This is overriding the ISO behavior. With ANSI_NULLS OFF, the comparison with NULL records results in TRUE; hence, records are returned. When the ANSI_NULLS OFF database option is used, SQL Server consider NULLs as special values when comparing with other records.

ANSI_NULLS Examples

Let’s create an employee temp table and populate it with some test records.

IF OBJECT_ID ('tempdb..#Employee') IS NOT NULL
 DROP TABLE #Employee;

CREATE TABLE #Employee
(
 Id            INT            IDENTITY (1 ,1)   PRIMARY KEY
,Name         VARCHAR (100)   NOT NULL
,Salary       INT              NULL
);

INSERT INTO #Employee VALUES
('Niraj' , 125), ('Vish'  ,80),
('Chetan', 235), ('Imans' , NULL);

SELECT  *
FROM   #Employee;

As seen above, we are selecting all records from the employee tables and highlighting NULL and NOT NULL records.

Differentiate NULL and NOT NULL Records

--Standard method to test for NULL
SELECT  *
FROM   #Employee
WHERE  Salary IS NULL;

SELECT  *
FROM   #Employee
WHERE  Salary IS NOT NULL;

The example above shows the standard method to retrieve NULL records from the table. This is the most recommended approach when dealing with NULL values.

SET ANSI_NULLS ON

--Set ANSI_NULLS option to ON
--Doesn't return any rows following ISO behaviour
SET ANSI_NULLS ON
SELECT *
FROM   #Employee
WHERE  Salary = NULL;

SELECT *
FROM   #Employee
WHERE  Salary <> NULL;

In the example above, ANSI_NULLS is ON. Therefore, the comparison with NULL values is evaluated as UNKNOWN, so no rows are returned.

SET ANSI_NULLS OFF

--Set ANSI_NULLS option to OFF
--Returns records having NULLs Overriding ISO Behaviour 
SET ANSI_NULLS OFF
SELECT *
FROM   #Employee
WHERE  Salary = NULL;

SELECT *
FROM   #Employee
WHERE  Salary <> NULL;

In the example above, ANSI_NULLS is OFF. Hence, a comparison with NULL values is evaluated as TRUE. This gives the NULL values a special meaning, and records are returned by the queries above.

Read more about Database and SQL programming from Tech-Recipes.