What does `SET ANSI_NULLS OFF` do?

Question

What does SET ANSI_NULLS OFF do?

Answer 1

From MSDN:

The SQL-92 standard requires equals (=) or non-equal (<>) comparisons with a null value to be evaluated as FALSE. When SET ANSI_NULLS is ON, the SELECT statement using WHERE column_name = NULL returns null rows, even if the column has null values. A SELECT statement using WHERE column_name <> NULL returns null rows, even if the column has non-zero values.

When SET ANSI_NULLS is turned off, the comparison operators Equals (=) and Not Equal To (<>) do not follow the SQL-92 standard. The SELECT statement using WHERE column_name = NULL returns rows with null values ​​in column_name. A SELECT statement using WHERE column_name <> NULL returns rows with non-zero values ​​in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ values ​​and are not NULL.

Answer 2

It changes the behavior of NULL . ANSI nulls give things like

NULL = NULL → false

NULL <> NULL → false

With ANSI_NULLS off, ( NULL = NULL ) -> true.

Answer 3

When enabled, it does not count null values ​​and returns 0.

When this is turned on, any query that compares a value with zero returns 0

Example: SET ANSI_NULLS ON SELECT empname FROM emp1 WHERE phone = NULL

Explanation: It will not return anything because SET ANSI_NULLS is enabled.

A source:

http://www.xpode.com/ShowArticle.aspx?ArticleId=599

Thanks,

Rohit

Answer 4

SET ANSI_NULLS OFF instructs the server to evaluate statements containing NULL using non-standard semantics.

 SET ANSI_NULLS OFF; SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Evaluates to 1 (bad!) SET ANSI_NULLS ON; SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Evaluates to 0 (good!) 

You should never create new code with the non-standard semantics parameter SET ANSI_NULLS OFF , because:

• It is never necessary
• For database queries that behave with rich semantics that occur when NULL processed differently than any other value (for example, in the WHERE ), values ​​compared to NULL should always return False / UNKNOWN.
• This makes the code more difficult to maintain, as developers may not realize that it uses custom configuration or is confused by it, and
• Microsoft warned that in a future version of SQL Server this option will cause a clear error.