June 3, 2013 at 11:19 pm
NBSteve (6/3/2013)
For anybody like myself who was still a bit unclear, it may help to think of COALESCE as having 2 separate aspects. It needs to return a value, and it needs to return a type. The rule for returning a value is simple enough... it returns the first non-NULL value. The rule for returning a type, however, is based on the highest data type precedence which means it may come from a different expression than the value comes from.To illustrate this, consider these two examples:
COALESCE(NULL, 1, 'A')
COALESCE(NULL, 'A', 1)
The value returned by COALESCE comes from the first non-NULL expression, 1 in the first case, 'A' in the second case. However, the type returned comes from data type precedence, and since int is a higher precedence than varchar, both (try to) return an int type. Of course, trying to return 'A' as an int type throws an error, meaning the first statement will work, and the second will not.
Great question, thanks!
+1 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 12:58 am
Thanx for the nice explanation
Please explain this
Select isnull ('A',1 ) does not return Error
select COALESCE( 'A', 1)
Conversion failed when converting the varchar value 'A' to data type int.
:w00t:
Neeraj Prasad Sharma
Sql Server Tutorials
June 4, 2013 at 1:50 am
Nice question.....
June 4, 2013 at 2:01 am
Neeraj Prasad Sharma (6/4/2013)
Thanx for the nice explanationPlease explain this
Select isnull ('A',1 ) does not return Error
select COALESCE( 'A', 1)
Conversion failed when converting the varchar value 'A' to data type int.
:w00t:
That is one of the many differences between COALESCE and ISNULL. For COALESCE, the return data type is determined by appplying data type precedence rules to all its arguments (as is the case with all other SQL expressions with mixed data types - with one exception). ISNULL is the exception to the normal data type precedence rules; its return data type is always equal to the data type of the first argument.
This is clearly documented in Books Online, by the way.
June 4, 2013 at 4:42 am
Thanx Hugo
got this
ISNULL : replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.
COALESCE : Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
Neeraj Prasad Sharma
Sql Server Tutorials
June 4, 2013 at 7:02 am
Does anyone know why this question now shows in the question list as Jul 8, 2013? Seems like this kind of thing happens occasionally here.
June 5, 2013 at 6:04 am
Primo Dang (6/4/2013)
Does anyone know why this question now shows in the question list as Jul 8, 2013? Seems like this kind of thing happens occasionally here.
Something to do with Dr Emmett Brown working for RedGate?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 5, 2013 at 6:21 am
Firstly, I was wondering how I answered this question, then realized it was repeated. By the way, Saturday do not have QOTD....
June 5, 2013 at 9:37 am
Wow - talk about DBCC Timewarp and some wormhole effect.
Reason being that the same question was for June 3 and June 8. Answer one and both get ticked as answered.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2013 at 8:36 pm
SQLRNNR (6/5/2013)
Wow - talk about DBCC Timewarp and some wormhole effect.Reason being that the same question was for June 3 and June 8. Answer one and both get ticked as answered.
You mean Steve (or someone else who might manage the QotD) set the same question for more than one day and so it appears pre-emptively on the second date, already answered?
That doesn't explain why the question for July 8th showed up over one month early... but DBCC Timewarp certainly does, I hadn't thought of that. There must be a parameter for question displacement.
June 6, 2013 at 1:27 am
Confused about why this appear again?:-P
June 12, 2013 at 1:53 am
Can someone change the date of this question? It is July but should be June! Question from the future )))
June 12, 2013 at 2:27 am
Evgeny (6/12/2013)
Can someone change the date of this question? It is July but should be June! Question from the future )))
My son would say "Chill your beans!!!" i.e. surely it is not essential?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 12, 2013 at 9:29 am
Gary Varga (6/12/2013)
Evgeny (6/12/2013)
Can someone change the date of this question? It is July but should be June! Question from the future )))My son would say "Chill your beans!!!" i.e. surely it is not essential?
I guess it might cause a problem on Monday 8 July when the real question for that day turns up. Anyway, the same question being listed for two days several weeks apart (3rd June and 8 July) does suggest that there's something a bit silly in the schema design for the data about QotD.
Tom
July 12, 2013 at 6:11 am
good guess .... bad response !
stupid boy !!
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply