SET_ANSI_NULLS Does Not Affect Column-to-Column Comparisons

I learned this fact the hard way the other day! A MERGE statement would not update null values in the destination table with the appropriate non-null data from the source table. My AND clause looked something like “Source.Name != Destination.Name”. I thought that setting ANSI_NULLS to OFF would cause this clause to catch differences when one of the values was null.

Nope!

NULL & Equals

By default, Microsoft SQL Server’s equals (=) and not equals (!=, <>) operators always evaluate to unknown (in effect, false) when one or both operands are null. This behavior is in accordance with the relevant ISO standard.

Setting the option ANSI_NULLS to OFF changes this behavior so that = and !=/<> behave identically to IS NULL and IS NOT NULL so long as at least one of the operands is either a literal null value or a variable set to null.

Column-to-Column

However, SET ANSI_NULLS OFF has no effect on comparisons between columns. If one or both of the column values are null, the following condition evaluates to false even if ANSI_NULLS is OFF.

WHERE Source.Name != Destination.Name

To match differences between the two columns, including cases when one or the other is null, a clause similar to the following must be used:

WHERE (
	(Source.Name != Destination.Name) OR
	(Source.Name IS NULL AND Destination.Name IS NOT NULL) OR
	(Source.Name IS NOT NULL AND Destination.Name IS NULL)
)

These rules apply to all uses of = and !=/<>, regardless of whether they occur in WHERE statements, JOIN conditions, AND clauses, or otherwise.

For more information on Microsoft SQL Server’s ANSI_NULLS option, visit MSDN.