How does ANSI_NULLS work in SQL

Hello friends, today I am going to talk about one more set statement that we see quite often in stored procedure scripts. We see this set option here and there in the script but we do not bother to understand why this statement is used?

Set ANSI_NULLS OFF: - Use of this statement enforce SQL Server database engine to compliant with ISO behavior of comparison operators (Equal (=) or Not Equal (<>)) against Null values. Let’s take an example to understand this fact.

I have created Employee table and inserted few rows into it using below script.

create table Employee(Eid int, Name varchar(50))

insert into Employee values(101,'Neeraj')

insert into Employee values(102,'Neha')

insert into Employee values(103,'Rachit')

insert into Employee values(104,Null)

insert into Employee values(105,Null)

In Last two insert statements, I have inserted null values in column “Name” of employee table. Now I want to select only those rows where column “Name” of employee table has null values.

Select * from Employee where Name=Null

Lets run the above select query and see the results.

In the above screen shot we can see the result set is blank, there are no records showing the null values for column “Name”.

Reason:- By default ANSI_NULLS setting is on , hence SQL database query engine is not able to compare the Null values when comparison operators(= or <>) is used. Let’s set the ANSI_NULLS OFF and see the results.

We can see the expected result is coming now. 

Since the ANSI_NULLS ON, is the default setting, hence we use “Is” operator to compare Null values and generally write quite similar queries as mentioned below to compare null values.

Select * from Employee where Name is Null

If we execute above query with setting up ANSI_NULLS on, the above query will also return expected result, Below is the screen shot for your reference.

Note:- Setting up Ansi_Nulls (On/Off) only applies to current session , rest all the sessions are intact.

I hope you will like this article. Please feel free to add your comments to share your thoughts on this article.