Question of the day
-
manik123 (1/25/2013)
nice question... i am bit confusion about the point "The return type of COALESCE does depend on the data type precedence rules". can u explain any body?????Manikandan.ps
Move fast as possible...................
Hi manik123
Datat type of the result returned by COALESCE will be highest precedence of all arguments used in the function. So all the arguments used inside Coalesce should be implictly convertible to the highest precedence otherwise u will get a conversion error.
For example.
SELECT COALESCE('abcd',GETDATE())Will return datatype conversion error.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Please refer this link for further detail & examples
http://msdn.microsoft.com/en-us/library/ms190349(SQL.105).aspx
--
Dineshbabu
Desire to learn new things.. -
Sean Pearce (1/25/2013)
Stuart Davies (1/25/2013)
Can't see there'll be much debate about this one!I vehemently disagree.
How can you possibly say that? :angry:
-
good question. My one answer is correct and another wrong. .:-)
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Good question.
I'm very surprised that more than half (113 people out of 218) so far have got it wrong. Maybe people don't use coalesce? Are that many people lucky enough to live with data where there are never any unknowns so that NULL isn't needed? Or always have little enough data and low enough performance requirements to be able to eliminate nulls by using a separate table for each attribute that could be absent and indicating its absence by not including the relevant row in that table?
Tom
-
L' Eomot Inversé (1/25/2013)
I'm very surprised that more than half (113 people out of 218) so far have got it wrong. Maybe people don't use coalesce? Are that many people lucky enough to live with data where there are never any unknowns so that NULL isn't needed?No, my guess is that
(a) there are still many people who only use ISNULL (because that is what was used in the code they copied when they started writing queries and they never changed their habit) and possibly don't know the existence of COALESCE;
(b) many people who do know both use one (either out of habit, or because they were once in a situation where the other one didn't work and now think that this means that one is "b etter" than the other without really knowing all the differences); and
(c) many people know both, know *some* of the differences, but don't know all of them.
I think it's encouraging that 92% of the people so far picked the correct answer on the number of parameters. I am not surprised that more people have problems with the possible answers on data type and ANSI. Surprisingly often, the data type rules ensure that the code "just works", so people don't have to know. And if you follow sound coding standards, you would ensure (with explicit CAST where needed) that all data types are compatible anyway.
Or always have little enough data and low enough performance requirements to be able to eliminate nulls by using a separate table for each attribute that could be absent and indicating its absence by not including the relevant row in that table?
That approach would not eliminate the need to use COALESCE or ISNULL; it would only make matters worse by requiring the use of all kinds of outer joins in your queries PLUS the use of COALESCE or ISNULL to handle the NULL values introduced by these outer joins.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (1/25/2013)
No, my guess is that(a) there are still many people who only use ISNULL (because that is what was used in the code they copied when they started writing queries and they never changed their habit) and possibly don't know the existence of COALESCE;
Hugo you are right. Many people doesn't know about existence of COALESCE. I have seen in the code written in earlier stages people using nested ISNULL for validating more than two parameters and the same has been still followed by their juniors.
--
Dineshbabu
Desire to learn new things.. -
Dineshbabu (1/25/2013)
Hugo Kornelis (1/25/2013)
No, my guess is that(a) there are still many people who only use ISNULL (because that is what was used in the code they copied when they started writing queries and they never changed their habit) and possibly don't know the existence of COALESCE;
Hugo you are right. Many people doesn't know about existence of COALESCE. I have seen in the code written in earlier stages people using nested ISNULL for validating more than two parameters and the same has been still followed by their juniors.
Three other factors may be at work: laziness, spelling,and inertia.
1. ISNULL is shorter and easier to type.
2. COALESCE s harder to spell, with it's silent "C". As it is a keyword that doesn't enjoy common usage in the English language, I have to stop and think how to spell it when I do use it.
3. Most of the cases you run into in early programming tend to be just testing for NULL and using a default if present. ISNULL works for that. Once in the habit of using ISNULL, I think inertia keeps you using it.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
I enjoyed the question thanks.
Dave Morris :alien:
"Measure twice, saw once"
-
Thomas Abraham (1/25/2013)
Three other factors may be at work: laziness, spelling,and inertia.1. ISNULL is shorter and easier to type.
2. COALESCE s harder to spell, with it's silent "C". As it is a keyword that doesn't enjoy common usage in the English language, I have to stop and think how to spell it when I do use it.
3. Most of the cases you run into in early programming tend to be just testing for NULL and using a default if present. ISNULL works for that. Once in the habit of using ISNULL, I think inertia keeps you using it.
+1
--
Dineshbabu
Desire to learn new things.. -
Thanks for the question, learned something today!
-
Sean Pearce (1/25/2013)
call.copse (1/25/2013)
Sean Pearce (1/25/2013)
Stuart Davies (1/25/2013)
Can't see there'll be much debate about this one!I vehemently disagree.
How can you possibly say that? :angry:
Just my Friday attempt at some humour.
Friday humour, on SSC - how dare you sir! (not so much debating - more arguing 😀 )
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Thanks for the question.
After all of the questions on COALESCE and ISNULL I was glad to feel confident on answering this one 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Hugo Kornelis (1/25/2013)
L' Eomot Inversé (1/25/2013)
....Or always have little enough data and low enough performance requirements to be able to eliminate nulls by using a separate table for each attribute that could be absent and indicating its absence by not including the relevant row in that table?
That approach would not eliminate the need to use COALESCE or ISNULL; it would only make matters worse by requiring the use of all kinds of outer joins in your queries PLUS the use of COALESCE or ISNULL to handle the NULL values introduced by these outer joins.
No, it's much worse than that. To avoid NULLs (and hence COALESCE and ISNULL) means also avoiding outer joins. Suppose you have what I think of as an outer join that would, in the situation you describe, result in a derived table with 4 potentially nullable columns: then, according to the Anti-Null Fundamentalists (ANFs), what you have to do is run 16 queries generating 16 derived tables each with a different signature and no nullable columns; you'll have to either materialise these inside SQL to do some more processing (always avoiding that absolutely banned outer join :w00t:) or pass them all back to the app layer. The purpose of passing them to the app is of course to avoid doing database functions in the database, because it's become too difficult to do them there, but then that's the way the ANFs think (if that's not a misuse of the verb "think" ;-)).
Perhaps I should have made it clear when I mentioned "performance" that I meant both DBMS performance and development team performance, not just the former. Note that if you have 10 nullable columns, you end up with 1024 derived tables, which will take an awful lot of code to deal with, code so complex that it will probably be wrong. Personally, I'll stick to having NULLs, and use coalesce and IsNull where appropriate.
Tom
-
Sean Pearce (1/24/2013)
I am getting myself confused with some simple maths :crazy:Answer 1
Coalesce can take more than two parameters, and IsNULL takes exactly two parameters
97%
Answer 5
Coalesce takes exactly two parameters, and IsNULL takes more than two parameters
6%
Does this mean somebody selected these two options as an answer?
I think the percentage only indicates the percent of responses submitted with that answer checked. In a "select n answers" scenario, those figures don't tell us anything about how many people check the correct n answers or how many people checked each of the possible combinations of n answers. In the case of this question, the 5 choices yield 10 distinct combinations of 2 answers (n choose k = n!/(k!(n-k)!) = 10 for n = 5 and k = 2).
Jason Wolfkill
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply