Ansii Nulls

Question

What is anssi null ? what is the advantage of use null is ON and OFF?what is the default? Why do we change the setting?

Muralidharan Dheenadhayalan · Answer

ANSI NULL : Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to ( ) comparison operators when used with null values. The SQL-92 standard requires that an equals (=) or not equal to ( ) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name . A SELECT statement using WHERE column_name NULL returns zero rows even if there are nonnull values in column_name . When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To ( ) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name . A SELECT statement using WHERE column_name NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name XYZ_value returns all rows that are not XYZ value and that are not NULL. http://msdn.microsoft.com/en-us/library/aa259229%28v=sql.80%29.aspx Hope this helps you.

Ansii Nulls

Answers (1)