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»»

Try it out.. Expand / Collapse
Author
Message
Posted Tuesday, August 03, 2010 8:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 4:24 AM
Points: 41, Visits: 102
Comments posted to this topic are about the item Try it out..
Post #963202
Posted Tuesday, August 03, 2010 8:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Nice question thanks!

Though instead of your explanation I mights say "While ISNULL returns that value with the type of the first parameter COALESCE returns the value with they type of the actual value being returned."

So it would be perfectly reasonable for a single COALESCE statement to return 5 or more different types depending on the inputs. (Well maybe not reasonable, but certainly possible.)

On the other hand a given ISNULL statement will always return the same type.
Post #963203
Posted Wednesday, August 04, 2010 1:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Nice Question.
Post #963264
Posted Wednesday, August 04, 2010 1:38 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
Good question. However an option with "GIVEN STRING IS NULL, GIV" would have been good. As the question stands its rather easy to guess. An inverted correct answer would have made it harder for people guessing (like me ).
Post #963266
Posted Wednesday, August 04, 2010 2:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 854, Visits: 513
Good Question. Thought both would truncate to 'GIV' and 'GIV'. Should have been more thoughtful.
Post #963272
Posted Wednesday, August 04, 2010 5:32 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: Thursday, April 03, 2014 10:34 AM
Points: 3,352, Visits: 1,478
Good question - it was obvious immediately what was being asked, which is always nice (I always have a problem with obscure/trick questions!).

Also, it's nice to be reminded of these things (I rarely use ISNULL - perhaps this is why.)

Duncan
Post #963367
Posted Wednesday, August 04, 2010 5:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 19, 2011 7:26 AM
Points: 1,078, Visits: 289
Good question - Learned something new.
Post #963375
Posted Wednesday, August 04, 2010 7:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Great question. I love QoTDs like this. They make me think and maybe read a little to make sure I understand something. Best of all QoTD is definitely making me better at all aspects of SQL Server. It is a great way to start the day and continually test and expand my knowledge.
Post #963456
Posted Wednesday, August 04, 2010 8:46 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: Monday, April 14, 2014 9:28 PM
Points: 554, Visits: 1,178
UMG Developer (8/3/2010)

So it would be perfectly reasonable for a single COALESCE statement to return 5 or more different types depending on the inputs. (Well maybe not reasonable, but certainly possible.)


Actually, with Coalesce it comes down to the expression with the highest data type precedence being returned, thus exactly 1 predictable type will be returned. Consider the following which results in an error.

DECLARE @STR int
SET @STR=NULL
SELECT COALESCE(@STR,'GIVEN STRING IS NULL',CAST(1 AS DATETIME))

Conversion failed when converting date and/or time from character string.


In terms of precedence, Datetime > Int > String, thus the int can be cast into a DateTime, but the String cannot, hence the error.
Post #963518
Posted Wednesday, August 04, 2010 10:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Bradley Deem (8/4/2010)[hrActually, with Coalesce it comes down to the expression with the highest data type precedence being returned, thus exactly 1 predictable type will be returned. Consider the following which results in an error.


Argh... Thanks for the correction, I knew that but for whatever reason wasn't thinking straight...
Post #963589
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse