April 17, 2015 at 12:12 am
Comments posted to this topic are about the item ISNULL vs COALESCE
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 17, 2015 at 1:41 am
Very nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 17, 2015 at 3:06 am
This was removed by the editor as SPAM
April 17, 2015 at 5:39 am
Stewart "Arturius" Campbell (4/17/2015)
Good question, thanks
April 17, 2015 at 6:04 am
Nice question to end the week.
April 17, 2015 at 6:14 am
I'm not sure it's accurate to say "all need to be of the same data type." I believe all need to be able to be implicitly converted to the data type of highest precedence. As evidence, if you replace that 'E' with a '1' you won't get that error.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
April 17, 2015 at 6:22 am
Great and helpful question. I didn't know that. Thanks!
April 17, 2015 at 6:38 am
The question is okay, but the explanation is absolutely incorrect.
The difference between the two functions is caused by some pretty non-standard behaviour of the ISNULL function. (Which is documented, by the way).
Normally, T-SQL expressions using mixed data types will return a data type as determined by the rules of data type precedence: https://msdn.microsoft.com/en-us/library/ms190309.aspx. Since int has a higher precedence than char, the COALESCE expression will return char(1). And since COALESCE is nothing but a shorthand for a CASE expression (see https://msdn.microsoft.com/en-us/library/ms190349.aspx), the COALESCE expression in the question will be evaluated as
CASE WHEN @char IS NOT NULL THEN CONVERT_IMPLICIT(int, @char) ELSE 0 END
When @char is NULL, 0 is returned. When @char is non-NULL, it will be converted to int. If @char is set to for instance '1', this will work - but when set to 'E', it will cause a run-time error.
ISNULL is non-standard in that the data type it returns is determined strictly by the first argument (see https://technet.microsoft.com/en-us/library/ms184325.aspx). So here, because @char is the first argument and @char is declared as CHAR(1), the result will be CHAR(1) and the integer constant 0 will be converted to the CHAR(1) value '0' when @char is NULL.
April 17, 2015 at 6:48 am
Hugo Kornelis (4/17/2015)
The question is okay, but the explanation is absolutely incorrect.
Thank you for stating it far more eloquently and intelligently than I did. 🙂
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
April 17, 2015 at 7:02 am
Hugo Kornelis (4/17/2015)
The question is okay, but the explanation is absolutely incorrect.The difference between the two functions is caused by some pretty non-standard behaviour of the ISNULL function. (Which is documented, by the way).
Normally, T-SQL expressions using mixed data types will return a data type as determined by the rules of data type precedence: https://msdn.microsoft.com/en-us/library/ms190309.aspx. Since int has a higher precedence than char, the COALESCE expression will return char(1). And since COALESCE is nothing but a shorthand for a CASE expression (see https://msdn.microsoft.com/en-us/library/ms190349.aspx), the COALESCE expression in the question will be evaluated as
CASE WHEN @char IS NOT NULL THEN CONVERT_IMPLICIT(int, @char) ELSE 0 ENDWhen @char is NULL, 0 is returned. When @char is non-NULL, it will be converted to int. If @char is set to for instance '1', this will work - but when set to 'E', it will cause a run-time error.
ISNULL is non-standard in that the data type it returns is determined strictly by the first argument (see https://technet.microsoft.com/en-us/library/ms184325.aspx). So here, because @char is the first argument and @char is declared as CHAR(1), the result will be CHAR(1) and the integer constant 0 will be converted to the CHAR(1) value '0' when @char is NULL.
Nice explanation Hugo.
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
April 17, 2015 at 9:27 am
ronmoses (4/17/2015)
I'm not sure it's accurate to say "all need to be of the same data type." I believe all need to be able to be implicitly converted to the data type of highest precedence. As evidence, if you replace that 'E' with a '1' you won't get that error.
Thanks to all who responded, maybe I should have used a little different wording. Not to bad for my first question submitted, I'll try and do better in the future.
The reason I did state it this way was one of the test I ran where there was more than one field listed in the COALESCE it appeared as if it was only looking at the data type if one of them was not NULL. I now see that if I would have used a different default value then zero and I would have got the conversion error.
Some other results from my testing:
DECLARE @char AS CHAR(1);
DECLARE @int AS INT;
SET @char = NULL;
SET @int = NULL;
SELECT COALESCE(@char, @int, 0); -- returns zero, no error
SET @char = 'E';
SELECT COALESCE(@char, @int, '0'); -- fails trying to convert 'E' to int
SELECT COALESCE(@char, @int, 'A'); -- fails trying to convert 'E' to int
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 17, 2015 at 9:38 am
Nice question and thanks for the further explanation Hugo!
April 17, 2015 at 10:49 am
Hugo Kornelis (4/17/2015)
The question is okay, but the explanation is absolutely incorrect.The difference between the two functions is caused by some pretty non-standard behaviour of the ISNULL function. (Which is documented, by the way).
Normally, T-SQL expressions using mixed data types will return a data type as determined by the rules of data type precedence: https://msdn.microsoft.com/en-us/library/ms190309.aspx. Since int has a higher precedence than char, the COALESCE expression will return char(1). And since COALESCE is nothing but a shorthand for a CASE expression (see https://msdn.microsoft.com/en-us/library/ms190349.aspx), the COALESCE expression in the question will be evaluated as
CASE WHEN @char IS NOT NULL THEN CONVERT_IMPLICIT(int, @char) ELSE 0 ENDWhen @char is NULL, 0 is returned. When @char is non-NULL, it will be converted to int. If @char is set to for instance '1', this will work - but when set to 'E', it will cause a run-time error.
ISNULL is non-standard in that the data type it returns is determined strictly by the first argument (see https://technet.microsoft.com/en-us/library/ms184325.aspx). So here, because @char is the first argument and @char is declared as CHAR(1), the result will be CHAR(1) and the integer constant 0 will be converted to the CHAR(1) value '0' when @char is NULL.
+1
Don Simpson
April 17, 2015 at 12:41 pm
Nice one to end the week with - thanks, below86!
And thanks to Hugo for his very erudite explanation!
April 17, 2015 at 1:10 pm
ronmoses (4/17/2015)
I'm not sure it's accurate to say "all need to be of the same data type." I believe all need to be able to be implicitly converted to the data type of highest precedence. As evidence, if you replace that 'E' with a '1' you won't get that error.
Agreed
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply