SET ANSI NULLS – SQL Server – SET Statements
SET ANSI_NULLS: It turns the ISO compliant for null comparison ON or OFF. If we set this setting to on then the select query that uses comparison operator = and <> will treat NULL as unknown value. So, it will not validate the null value in the column. If it is off then it treats null value as null and we can use = or <> operators to check for null value in the column.
ANSI_NULLS setting does not apply to IS NULL or IS NOT NULL in the where clause
I have a table with few rows as shown below.
We have got zero rows. Now, I am going to execution the same query with the ANSI_NULLS OFF.
Now, we got 3 rows in the result.
Another experiment for you. Try using <> NULL in the where clause.
We have learned how the use of SET ANSI_NULLS in this blog post.
Note: In a future version of SQL Server, ANSI_NULLS will always be ON. So, avoid using this in your new project.