Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by John Arnott » More Fun With NULL
Add to briefcase
18 posts, Page 2 of 2
««12

More Fun With NULL Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jts_2003
Posted Monday, November 09, 2009 9:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 1,060, Visits: 4,168
Option 6 fooled me too - but I'm always glad to learn more about the differnces between ISNULL and COALESCE, since they often appear to be interchangeable!
Post #815854
Michael Poppers
Posted Monday, November 09, 2009 9:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:34 PM
Points: 565, Visits: 360
majorbloodnock (11/9/2009)
Which of these seven queries will return "Date is Null"? (select all that apply)


Was that a zero-based list?

Noticed that, but since the PRINT statements utilize 1 through 8, the answer is clear .

Nice question, John!
Post #815892
SanjayAttray
Posted Monday, November 09, 2009 10:43 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
Interesting question.

I got it right just because I executed it in SSMS and saw results. But without executing I would had selected option 6 also.

SQL DBA.
Post #815956
SQLRNNR
Posted Monday, November 09, 2009 11:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332
Doh, option 6 tripped me up - should have read it more closely.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #815974
john.arnott
Posted Monday, November 09, 2009 11:48 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
Michael Poppers (11/9/2009)
majorbloodnock (11/9/2009)
Which of these seven queries will return "Date is Null"? (select all that apply)


Was that a zero-based list?

Noticed that, but since the PRINT statements utilize 1 through 8, the answer is clear .

Nice question, John!

Major:
After doing a Homer Simpson impression ("Doh!"), I had to laugh. Even after re-reading several times, I missed that (seven vs eight). I guess that's why we are taught to have others review our work.

Michael (and others),
Thank you for the positive feedback.
I probably should have mentioned in the "Explanation" that the easiest way to see the full picture is to run the script after switching to "Results to Text".

------
edit: minor typo: "picture", not "pecture".
Post #816007
KevinC.
Posted Monday, November 09, 2009 12:02 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 12:55 AM
Points: 771, Visits: 504
Nice question. It really got me thinking (and on a Monday, too!)
Post #816020
christopher.gray
Posted Tuesday, November 10, 2009 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:13 AM
Points: 5, Visits: 86
About the author's discussion of Option 5...

"Option 5 is am especially delicious failure in that it looks just like the sucessful option 3, only using ISNULL instead of COALESCE. It fails because the value that ISNULL will substitute for a found NULL must be the same (or capable of being implicitly converted to the same) datatype as the tested value. The COALESCE function does not enforce datatyping, so Option 3 works as is, but in a broader scope, one should beware of how the output of the COALESCE is used -- it may still subsequently encounter a datatype mis-match "

To me, it seems like this is implying that COALESCE doesn't type-check, but the examples don't compare apples to apples.... "The successful Option 3" looks like this:

coalesce(convert(char(10),@MyDate,101),'Date is Null')

... the use of CONVERT means both parameters to COALESCE are varchars. Option 3 doesn't skip type-checking - the (base) types just happen to be the same. Try it like this:

coalesce(@MyDate,'Date is Null')

... and you get the same error the IsNull in Option 5 gives.
Post #816520
john.arnott
Posted Tuesday, November 10, 2009 11:20 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
Christopher,

You're absolutely right. I feel a bit embarrassed, but have to agree. Your counter example is actually in the list as option 1, so I have no excuse.

So, it looks as though the one difference is how ISNULL will use a stricter version of the first element's data description, truncating the contents of the second element to that length. Compare successful option 3 with truncated option 6:

Select coalesce(convert(char(10),@MyDate,101),'Date is Null') --returns 'Date is Null'
 Select isnull(convert(char(10),@MyDate,101),'Date is Null') --returns 'Date is Nu'

Even though the CONVERT specifies 10 characters in both, only the ISNULL truncates the literal message.

Thank you again for pointing out my confusion with option 5.
Post #816697
« Prev Topic | Next Topic »

Add to briefcase
18 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.