Tom.Thomson (10/27/2009)
Kevin Gill (8/14/2009)
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)
-- Kev
Although the version with coalesce does work when both @a and @b-2 are null, it will also deliver TRUE is one of them is null and the other is -1; so it isn't something you should use.
Sorry I was considering it as a given that you do not coalesce them to anything that they can possibly be - for example if it's a quantity then negative numbers are safe, if it's a date then the beginning or end of time would generally be safe. My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.
Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...
-------------------------------Oh no!