What Does “SET ANSI_NULLS ON” Mean In SQL Server ?

Most of us would have seen mentioning “SET ANSI_NULLS ON | OFF” setting at the start of a stored procedure, function etc. Lets understand why we keep that always.

ANSI_NULLS is simply a setting that controls the behavior of the Equals (=) and Not Equal To (<>,!=) comparison operators when they are used with null data field values in queries.

SET ANSI_NULLS ON

When we set it ON, A SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

NULL means something we don’t know(UNKNOWN). So we can’t compare one unknown value with another using = or <> operator. It results into UNKNOWN and rows are discarded in the result set

We can only say IS NULL or IS NOT NULL if required.

The following table shows how the setting of SET ANSI_NULLS ON affects the results of a number of Boolean expressions using null and non-null values.

NULL = NULL UNKNOWN
1 = NULLUNKNOWN
NULL <> NULLUNKNOWN
NULL>NULLUNKNOWN

SET ANSI_NULLS OFF

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns all the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns all the rows that have non-null values in column_name.

The following table shows how the setting of SET ANSI_NULLS OFF affects the results of a number of Boolean expressions using null and non-null values.

NULL = NULL TRUE
1 = NULL FALSE
NULL <> NULL FALSE
NULL>NULL UNKNOWN

Examples :

Let’s understand this by real examples. Run the below sample script for demo.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Drop the Table if already exists
DROP TABLE  IF EXISTS CITY
 
-- Create a City Table
CREATE TABLE City(
	CityID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	CityName nvarchar(100),
	StateName nvarchar(100)
)
 
-- Insert some data to it
INSERT INTO City (CityName,StateName) VALUES('Bangalore','Karnataka'),
(NULL,'Karnataka'),('Mangalore','Karnataka'),
('Vijayapura','Karnataka'),('Belgaum','Karnataka')
,(NULL,'Kerela'),('Kochi','Kerela')
 
-- See the city data
SELECT * FROM City

The above scripts create a table City and inserts some dummy data into it.

Example 1 : Get the Records where CityName is NULL

1
2
3
4
-- Example 1 : Get the Records where CityName is NULL
SET ANSI_NULLS ON
SELECT * FROM City 
	WHERE CityName=NULL

It returns no rows even though there are two records where cityName column value is NULL. This is because, SQL Query Engine evaluates UNKNOWN values while comparing with NULL value to the cityname column and discards those unknown values.

Let’s try the same query by turning ANSI_NULLS setting to OFF.

1
2
3
SET ANSI_NULLS OFF
SELECT * FROM City 
	WHERE CityName=NULL

This time, it returns the both records because SQL Query Engine evaluates the comparison with NULL to TRUE if cityname column having NULL value.

So that’s all about the ANSI_NULLS settings.

NOTE :
In a future version of SQL Server, ANSI_NULLS will 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.

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning đź™‚

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.