Indeed. ANSI NULLS disabling is just a workaround I think for backwards compatibility with something.

Essentially an 'IF A = B' statement is saying that is (A = B) returns true, then do whatever the IF suggests. so...

With ANSI NULLS ON :

NULL = NULL returns UNKNOWN so 'IF NULL = NULL will go down the ELSE route

NULL NULL returns UNKNOWN so 'IF NULL NULL will go down the ELSE route

NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route

NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

The difference with ANSI NULLS OFF is simply that the = operator and the 'IS' operator are treated the same. Instead of being treated as an unspecified value, NULL is treated as a 'value of NULL' if you see what I mean. Therefore

With ANSI NULLS OFF :

NULL = NULL returns TRUE so 'IF NULL = NULL will go down the IF route

NULL NULL returns FALSE so 'IF NULL NULL will go down the ELSE route

NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route

NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

The main impact of this that I've come across is that if you have two variables and both are null, the only complete equality check with ANSI NULLS on is :

IF @a = @b-2 OR (@a IS NULL AND @b-2 IS NULL)

or shortened using my favourite TSQL function, to

IF COALESCE(@a,-1) = COALESCE(@b,-1)

Check the execution plan though, sometimes the longhand approach is considered more efficient if performance is important.

-- Kev