COALESCE
-
L' Eomot Inversé (10/17/2012)
Probably the worst explanation I've ever seen - two gross mistakes, which unfortunately cancel each other out so that people might be misled into believing them. It starts with the bizarre idea that coalesce called with two non-null arguments returns null, and ends with the idea that a typed null ehen concatenated with a string magically turns from null to 'NULL'! That definitely needs fixing.While I agree that the explanation is incorrect, I would not call it the worst explanation ever. I think (as you can read in my previous reply) that the author refers to "the string value 'NULL'" when he write "a typed NULL". If you do a global search and replace to fix this, the expllanation actually makes sense:
"The innermost coalesce returns the string value 'NULL' after which the second coalesce is executed which in turn returns again the string value 'NULL' as the variable @a is holding a null value. The isnull function returns the same string value 'NULL' which is then appended to the value Fail."
(And for the true nitpickers, the last words ought to be changed to "to which the value 'Fail' is then appended).
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 (10/17/2012)
The explanation is wrong, though. The innermost COALESCE does NOT return "a typed NULL", but a non-NULL string value (that just happens to be equal to the string 'NULL'). And since this is a regular, non-NULL value, all other COALESCE and ISNULL calls simply let it bubble up.
Just for the record: the term "a typed NULL" does not mean (what the author of the question apparently thinks) "the word NULL, just typed in by me on my keyword". It means "a NULL value with data type association". This is contrast with an untyped NULL, which, obviously, is a NULL value with no data type association.
....<etc />....
Yeah, what Hugo said. I noticed this as well but as ever his explanation is a lot more complete than I could have put together.
Other than that, I think this was a decent question. It may not have used COALESCE in the most strictly correct way but it did check for a reasonable understanding of a couple of useful functions that work with NULLs. Not every question needs to be stretching the skills of the SQL rockstars on the forum, when I first started reading SQLServerCentral that would have been new information to me and it's good to cater to a range of levels of knowledge and experience.
-
Good question. Just started using COALESCE fairly recently so it was a good exercise. Spotted the quoted NULL but it is a little tricky away from the COALESCE but I guess everything has to be considered in the code.
-
philip.cullingworth (10/17/2012)
Lokesh,Thanks for correcting my mistake. What comes of trying to write a response before the coffee has kicked in.
Philip
You are welcome 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
kapil_kk (10/17/2012)
But the query that you pasted is just simply give the first non null value as the coalesce does and give output as 'NULLFail'.... 😛 (no tricks in it)today's QOD is little tricky to make confusion between null and 'NULL'
I am not sure about your statement regarding nesting of coalesce but I think we can do that...
Yes result is same, but the query isn't. What if @a is a "not-null" value.... result of both the queries vary 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks! Interesting, made me think.
-
Thanks for the question! =]
For a moment, I thought either COALESCE or ISNULL interpreted 'NULL' as NULL and that was the point of the question, but I decided to go with what I knew and got it right.
-
good explanation Hugo 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Good Question for the middle of the week. +1
-
Been trying to figure out what blind alley my brain went down to get this *wrong*--I correctly got the entire thing down to ISNULL('NULL', 'Pass') + 'Fail', but then somehow resolved that to just Fail! :ermm:
-
Thanks Hugo. It's becoming usual that your explanation be better than the author's explanation of the QoD.
And that's why I read the related posts after answering.
-
Hugo Kornelis (10/17/2012)
I was almost hesitant to answer the question, thinking there might be a trick thaht I was overlooking. Fortunately, I wasn't.The explanation is wrong, though. The innermost COALESCE does NOT return "a typed NULL", but a non-NULL string value (that just happens to be equal to the string 'NULL'). And since this is a regular, non-NULL value, all other COALESCE and ISNULL calls simply let it bubble up.
Just for the record: the term "a typed NULL" does not mean (what the author of the question apparently thinks) "the word NULL, just typed in by me on my keyword". It means "a NULL value with data type association". This is contrast with an untyped NULL, which, obviously, is a NULL value with no data type association.
The constant (without quotes) NULL is technically an untyped NULL. At some point, SQL Server will convert this to a typed NULL, choosing the data type based on context. If I execute "SELECT NULL + 1, NULL + 'Text', DATEADD(day, 12, NULL);", the three untyped NULLS will be converted to respectively integer, varchar(??), and one of the date/time types. If you just execute "SELECT NULL;", SQL Server will have to choose on a data type before sending it to the client, because the protocol for communication between SQL Server and the client doesn't support untyped data. I think the choice will be integer, but I'm not sure.
A typed NULL can be achieved by using a CAST or CONVERT expression. For instance CAST(NULL as varchar(20)) is a NULL, typed as varchar(20). One way to see this in effect is to set SSMS to use "output as text", and then execute for instance "SELECT CAST(NULL as int), CAST(NULL as varchar(4)), CAST(NULL AS numeric(15,2)), CAST(NULL AS float), CAST(NULL AS datetime2);". Each of the output columns has a different length, based on the data type of the column.
Most of the time, there is no practical difference between using typed or untyped nulls. But someimes, you do need to use a NULL value, and the default choice SQL Server makes when choosing its data type is wrong - in that case, using an explicitly typed NULL is the only option.
PS: Sorry for going off on a tangent... None of the above is even remotely related to the question (though it is somewhat related to the mistake in the explanation).
Thanks for an excellent explanation.
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 -
Hugo, excellent, so when is the next book going to print?
Not all gray hairs are Dinosaurs!
-
Primo Dang (10/17/2012)
Thanks for the question! =]For a moment, I thought either COALESCE or ISNULL interpreted 'NULL' as NULL and that was the point of the question, but I decided to go with what I knew and got it right.
That was my take, and why I didn't view it as a trick question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Narud (10/17/2012)
Thanks Hugo. It's becoming usual that your explanation be better than the author's explanation of the QoD.And that's why I read the related posts after answering.
And that's why I want to be just like Hugo when I grow up. Just when that will be remains to be seen. :hehe:
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply