While creating Stored Procedures, User Defined Functions etc, most of us use the SET ANSI_NULLS ON/OFF and SET QUOTED_IDENTIFIER ON/OFF Settings. In this article will discuss on the SET ANSI_NULLS  { ON | OFF } Setting.

To understand this with an example, let us create table Name and insert three records in this table as below:

CREATE TABLE dbo.Name(FirstName VARCHAR(50),LastName Varchar(50)) GO 
INSERT INTO dbo.Name VALUES('BASAVARAJ','BIRADAR'),
 ('KALPANA','PATIL'),
 ('MONTY', NULL) GO

Depending on the ANSI_NULLS setting value either ON or OFF, SQL Server behaves differently while comparing with NULL value.

SET ANSI_NULLS  ON

When this setting value is ON (i.e. SET ANSI_NULLS ON) then comparison with NULL value using = and <> comparison operator will return false. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL
Result:
FirstName               LastName 
----------------------- --------------------------
(0 row(s) affected)
FirstName               LastName
----------------------- --------------------------
(0 row(s) affected)

So, when this setting value is ON we need to use IS NULL or IS NOT NULL instead of comparison operator = and <>. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NOT NULL 
Result:
FirstName                LastName 
------------------------ ------------------------ 
MONTY                    NULL 
(1 row(s) affected) 
FirstName                LastName 
------------------------ ------------------------ 
BASAVARAJ                BIRADAR 
KALPANA                  PATIL 
(2 row(s) affected)

SET ANSI_NULLS  OFF

On the other hand if this setting value is OFF (i.e. SET ANSI_NULLS OFF) then comparison with NULL value using = and <> comparison operator returns TRUE if the value to be compared is NULL and NON NULL value respectively. Below Script demonstrates this fact.

SET ANSI_NULLS OFF 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL 
Result:
FirstName               LastName 
----------------------- --------------------- 
MONTY                   NULL 
(1 row(s) affected) 
FirstName               LastName 
----------------------- --------------------- 
BASAVARAJ               BIRADAR 
KALPANA                 PATIL 
(2 row(s) affected)

Another important point to note is that, the SET ANSI_NULLS { ON| OFF } setting with which we create Stored Procedure/UDF etc will be stored in the meta data. So, whenever Stored Procedure/UDF executes, it will use these setting stored in the meta data. It will ignore the settings of the client or the calling application.

 Below query can be used to find the objects which are created with SET ANSI_NULLS setting as OFF.

SELECT OBJECT_NAME (object_id) FROM sys.sql_modules
WHERE uses_ansi_nulls = 0 -- 0 means OFF and 1 means ON

As per BOL for SQL Server 2008 or 2012, in future versions this setting value will always be ON and explicitly setting it to OFF will result in error. So, it is better avoid explicitly setting  this in future development work.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version