SET ANSI_NULLS (ON | OFF) in SQL Server

feature-sql1-618x350

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;

Employee_table_NULL_Record

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;

IS_NULL_OR_IS_NOT_NULL_SQL_Server_1

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;

ANSI_NULLS_ON_SQL_Server

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;

ANSI_NULLS_OFF_SQL_Server

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.