Use of Set ANSI_NULL ON / OFF in SQL Server

Use of Set ANSI_NULL ON / OFF in SQL Server

Hint: It is used while creating user defined functions, stored procedures and etc

Let we create table with name of customers,

CODE

Create table dbo.customer (CustId varchar(5), CustFirstName varchar(25), CustLastName varchar(25))

Go

Insert into dbo.customer (CustId, CustFirstName, CustLastName) values ('CU001', 'SURESH', 'BABU'), ('CU002', 'RAVI', 'KUMAR'), ('CU003', 'AVINASH', NULL), ('CU004', 'MAHESH', NULL)

Now run below SQL Query to extract the values in the table of dbo.customer

set ansi_nulls on

select * from dbo.customer where CustLastName = null

It will yields “0” results like

CustId

CustFirstName

CustLastName

And in the Message column it shows

(0 row(s) affected)

Again you run with another code but little modification

“

set ansi_nulls off

select * from dbo.customer where CustLastName = null

it will yields you following results,

CustId	CustFirstName	CustLastName
CU003	AVINASH	NULL
CU004	MAHESH	NULL

And in the message column,

(2 row(s) affected)

Hint: If it is ansi_nulls on

We need to use following code to extract records with null values

set ansi_nulls on

select * from dbo.customer where CustLastName is null

or

set ansi_nulls on

select * from dbo.customer where CustLastName is not null

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