Why we use ANSI_NULLS ON/OFF

You may have seen many times SET ANSI_NULLS ON statement on top of MS SQL Server stored procedures sometime on functions. Have you ever wondered what is it really means? Well if you havent you may be not reading this blog post.

So the main purpose of this option is to give SQL Server the ISO complient standard to its queries which uses Equal (=) and Not Equal (<>) operators on nullable columns. We should note using these two operators on nullable columns is not ANSII (or SQL 92) standartd. If you set this option ON then in SELECT statements with any conditions in the WHERE cluase which uses column = NULL or column <> NULL can get affected.. These conditions will not return any data if there are any NULL values in that column.

I know this is not that clear to you, so lets do a simple example to clear things out.

First we'll create a sample table and populate it with some test data;

CREATE TABLE tblTestNulls (ID INT IDENTITY NOT NULL PRIMARY KEY, [Name] VARCHAR(20) NULL) 

INSERT INTO tblTestNulls
SELECT 'Johon' UNION ALL
SELECT 'Mayor' UNION ALL
SELECT NULL UNION ALL
SELECT NULL Then we'll run below two queries with ANSI_NULLS ON.

SET ANSI_NULLS ON
SELECT  * FROM tblTestNulls WHERE [Name] = NULL
SELECT
  * FROM tblTestNulls WHERE [Name] <> NULL

As you can see it does not return anything since there are NULL values in Name column and we have set ANSI_NULLS ON.
So we'll run the same queries with ANSI_NULLS OFF
SET ANSI_NULLS OFF
SELECT  * FROM tblTestNulls WHERE [Name] = NULL SELECT * FROM tblTestNulls WHERE [Name] <> NULL 

So here we get the desired results. If we dont set any option for ANSI_NULLS default would be database default, usually OFF unless you have changed it. The database's default ANSI_NULLS settings you can find under database properties then going to options page.

Also if you use ANSII syntax for NULL comparison such is IS NULL you can get the desired results regardless of  whether ANSII_NULLS is ON or OFF. So you can write our two queries as below;

SELECT * FROM tblTestNulls WHERE [Name] IS NULL
SELECT * FROM tblTestNulls WHERE [Name] IS NOT NULL

This is one of the good example why it is always better to use ANSII syntax in Transact SQL.
Conclusion here is dont use SET ANSII_NULL ON simply because it is there in the SQL Server template when you try to create a Stored Procedure or Function. If it is ON adhere to the ANSII syntaxes. Also it is important if your queries run on hetrogenius databases.

Please feel free to leave your comments.