Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

More Fun With NULL Expand / Collapse
Author
Message
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: Tuesday, April 08, 2014 6:23 AM
Points: 1,082, Visits: 4,887
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
Posted Monday, November 09, 2009 9:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:41 PM
Points: 634, Visits: 399
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
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, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
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
Posted Monday, November 09, 2009 11:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 20,455, Visits: 14,075
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


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
Posted Monday, November 09, 2009 11:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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
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
Posted Tuesday, November 10, 2009 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 5:35 AM
Points: 19, Visits: 111
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
Posted Tuesday, November 10, 2009 11:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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
Posted Tuesday, August 13, 2013 6:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:50 PM
Points: 1,867, Visits: 2,275
Excellent Question........
Thanks a lot



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1483694
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse