SET ANSI_NULLS ON/OFF Setting in Sql Server

Most often we see in SQL server query analyzer, it is written by experts T-SQL programmer - SET ANSI_NULLS ON/OFF, To better understand this I have followed few articles and providing the below explanation with an example.

This option is useful for NULL values comparison. When we set it to ON it follows the ISO standard and according to it NULL values can be compared only with IS or IS NOT keywords.

Refer the below example for better understanding -

Let's create a table and insert few records with NULL values, then we will apply search on NULL values with different situations.

Create a table

Create table #Temp

(

Id int identity(1,1)

, Name Varchar(100)

)

Insert records with NULL values

Insert into #Temp(Name) Values('John'),('Michel'),(NULL)

Above table will look like -

Set ANSI_NULLS ON

When we SET ANSI_NULLS ON, It follows the ISO standard while searching for NULL values. According to the ISO standard, always use IS or IS NOT key words while searching for the NULL values. If we wan to search NULL values by '=' or '<>' operators it will not give any result in this case.

Refer the below results

SET ANSI_NULLS ON 

SELECT *FROM #TEMP WHERE NAME IS NULL

SET ANSI_NULLS ON 

SELECT *FROM #TEMP WHERE NAME IS NULL

Above results are as expected.

Apply '=' or '<>' operator for searching of null values

SET ANSI_NULLS ON 

SELECT *FROM #TEMP WHERE NAME = NULL

and

SET ANSI_NULLS ON 

SELECT *FROM #TEMP WHERE NAME <> NULL

Both of the queries with '=' and '<>' will give the empty result as above and this is not as per expectation because ISO standard do not allow to use '=' or '<>' operator to apply search on NULL values.

Set ANSI_NULLS OFF

We can use '=' and '<>' operators with search on NULL values if we set ANSI_NULLS to OFF.

Refer the below results

SET ANSI_NULLS OFF 

SELECT *FROM #TEMP WHERE NAME = NULL

SET ANSI_NULLS OFF

SELECT *FROM #TEMP WHERE NAME <> NULL

Now we can see the above results and it is as per expectation.

Important points -

1. Search on NULL value will always work with IS and IS NOT keywords. If in case it is required to apply search on NULL values based on '=' or '<>' operators then Set ANSI_NULLS OFF, It will produce the result as expected.

2. It is followed as good practice to Set ANSI_NULLS ON.

3. Do not use  Set ANSI_NULLS ON inside the stored procedures

4. Indexed view and computed column requires SET ANSI_NULLS ON otherwise UPDATE/INSERT will be failed.

5.   5. Always use SET ANSI_NULLS ON while creating stored procedures or indexes.

      

     I hope above post will be useful. 

d

I