August 26, 2009 at 1:42 am
SQAPro (8/25/2009)
...snip...
ISNULL is your friend!
Or the ANSI standard COALESCE for the ANSI pedants among us :o) Has the added advantage of being able to take more than two operands. It will simply return the first non-null one. I believe ISNULL is limited to two.
declare @i char(1), @j-2 varchar(1)
SELECT COALESCE(@i, @j-2, 'Both Missing')
-------------------------------Oh no!
October 27, 2009 at 9:01 am
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.
Tom
October 28, 2009 at 3:54 am
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!
March 23, 2012 at 3:54 pm
I run your question with
SET ANSI_NULLS ON and SET ANSI_NULLS OFF, and the result is same. I use SQL Server 2012
?
hm
?
Igor Micev,My blog: www.igormicev.com
March 23, 2012 at 5:03 pm
Kevin Gill (10/28/2009)
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.
Identity columns can easy be negative. Create them with a negative seed, or with a negative increment, or both is one way. setting identity insert on and placing explicit negative values is another. They are quite common when a large range is needed (bigger than 2 billion) - an identity column starting with seed 2**-31 and increment +1 gives the maximum possible range of values.
Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...
OK, maybe it should have been "it isn't something you should use unless you are absolutely certain you can pick a null-replacement value that is guaranteed to be safe". But remember that Murphy's law applies, so that most people will sometimes choose an "absolutely guaranteed safe" value that isn't safe at all.
Tom
Viewing 5 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply