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 = NULL||UNKNOWN|
|NULL <> NULL||UNKNOWN|
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|
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.
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.