April 18, 2015 at 9:32 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.
I agree with all the above.
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.
I actually agree with that too, but think it's badly phrased - "the type it returns is determined strictly by he first argument" might be interpreted as "the value it returns has the same type as the first argument" which is not quite accurate, because the first argument may have no type (ie it's an untyped NULL) and the second have a type (even if the second argument is a typed null) and in that case the returned value always has a type - it's the type of the second argument. It is still determined by the first argument (but not by the first argument alone) but only in that it's the first argument's lack of any type that makes T-SQL choose to return something with the type of the second argument.
In the crazy case (crazy because providing an untyped null as the second argument of ISNULL isn't exactly a sane thing to do) where both arguments are untyped nulls I'm not sure what type the returned NULL has but I think it's probably INT; maybe that's what the nonsense in BOL about returning an INT when the first argument is untyped and the second argument is not provided means - I say "nonsense in BOL" because ISNULL with an untyped NULL first argument and without a second argument raises an error instead of returning the INT value claimed by BOL.
Tom
April 18, 2015 at 10:28 am
TomThomson (4/18/2015)
In the crazy case (crazy because providing an untyped null as the second argument of ISNULL isn't exactly a sane thing to do) where both arguments are untyped nulls I'm not sure what type the returned NULL has but I think it's probably INT; maybe that's what the nonsense in BOL about returning an INT when the first argument is untyped and the second argument is not provided means - I say "nonsense in BOL" because ISNULL with an untyped NULL first argument and without a second argument raises an error instead of returning the INT value claimed by BOL.
Thanks for the additional information, Tom!
I personally think the first case is already crazy (who would ever type a literal NULL as the first argument to ISNULL anyway??). But for the sake of geeking out, I decided to do some experimenting.
ISNULL(NULL, NULL) does indeed evaluate to int. I verified this by running the code below and then checking the table properties in SSMS.
SELECT ISNULL(NULL,NULL) AS x INTO dbo.y1;
However - it does get more interesting. Because my next experiment, running the code below, should have resulted in an error when trying to convert the string constant 'x' to an integer, but instead it created a table with a varchar(2) column - so now the data type resulting from ISNULL has apparently suddenly changed to varchar(1)!!
SELECT ISNULL(NULL,NULL) + 'x' AS x INTO dbo.y2;
As I said before, all very weird, completely non-standard. One of many reasons why I try to avoid ISNULL and instead use COALESCE whenever I can.a
April 18, 2015 at 11:44 pm
Aleksl-294755 (4/17/2015)
Great and helpful question. I didn't know that. Thanks!
+ 1, I am not using COALESCE much lately.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
April 19, 2015 at 7:32 am
Hugo Kornelis (4/18/2015)
I personally think the first case is already crazy (who would ever type a literal NULL as the first argument to ISNULL anyway??). But for the sake of geeking out, I decided to do some experimenting.
Yes, you are right, it is just as crazy as the other case. In both cases the only reason to do it is geekery, trying to find out what happens.
ISNULL(NULL, NULL) does indeed evaluate to int. I verified this by running the code below and then checking the table properties in SSMS.
SELECT ISNULL(NULL,NULL) AS x INTO dbo.y1;
Unfortunately that doesn't tell us that ISNULL is returning a NULL typed as INT: this too generates atable with an INT column
so ISNULL could be returning an untyped NULL and the generated column would still be type INT. of course i should be impossible for a function to return an untyped NULL, but we are in weird territory here.
However - it does get more interesting. Because my next experiment, running the code below, should have resulted in an error when trying to convert the string constant 'x' to an integer, but instead it created a table with a varchar(2) column - so now the data type resulting from ISNULL has apparently suddenly changed to varchar(1)!!
SELECT ISNULL(NULL,NULL) + 'x' AS x INTO dbo.y2;
[/quote-2]
That's very interesting. Thanks for pointing it out, Hugo.
It should work if the type of the result from ISNULL has lower precedence that a string tpe, so it would work if the "value" delivered by ISNULL(NULL,NULL) is an untyped NULL.
So I think your experiments have demonstrated that I was wrong about the result being a NULL of type INT, and that this function can after all return an untyped NULL as its result.
As I said before, all very weird, completely non-standard. One of many reasons why I try to avoid ISNULL and instead use COALESCE whenever I can.
Certainly it's weird. But it's less weird than calling a function with COALESCE(<expression>,0) as its argument and getting an error because the argument is NULL.
ISNULL works sensibly under any isolation level, COALESCE is stable only under snapshot and serializable isolation levels, and shouldn't be used under repeatable-read or read-committed isolation levels without being absolutely certain either that it doesn't matter if the instability causes a NULL to be returned which wouldn't have been returned if coalesce were deterministic/stable or that other actions going on can't make this happen. The nullability of a non-null result from COALESCE can cause problems too (in SELECT...INTO statements). These problems with coalesce are pointed out very clearly in BOL, with advice that in situations where a spurious NULL can cause problems one should either use ISNULL instead of COALESCE or use a high isolation level so that COALESCE won't cause instability problems.
It's also unfortunate that the SQL specification didn't state that errors caused by speculative evaluation of non-chosen paths should be fully contained, as they are in every other language I've worked with that uses speculative evaluation.
COALESCE is attractive because (a) it is standard and (b) it has a neater syntax than ISNULL; and it's unattractive because (a) it's unstable except at the two highest isolation levels and (b) its result always has a nullable type and (c) it can deliver null where ISNULL wouldn't and (d) it forces use of some sort of work-around (either ISNULL or something much nastier) to avoid unwanted errors during COALESCE's speculative evaluation.
I think that most of the time I can use COALESCE, but it's clear that there are situations where COALESCE is dangerous and unusable and ISNULL is the tool for the job - when the problem with COALESCE is the speculative evaluation of results imposed by its description as a CASE statement the only way to avoid using ISNULL is to write a series of IF statements to do the coalesce function safely - and I think that using control flow because the standard forgot to say that speculative evalution is required to be safe is a worse option than using ISNULL.
Tom
April 20, 2015 at 8:09 am
COALESCE is attractive because (a) it is standard and (b) it has a neater syntax than ISNULL
Both are reasons I started to try and use this in my code, until I ran into the issue that prompted me to create this question.:-)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 20, 2015 at 8:35 am
Thanks for the question. I didn't know that one.
April 21, 2015 at 1:32 am
below86 (4/20/2015)
COALESCE is attractive because (a) it is standard and (b) it has a neater syntax than ISNULL
Both are reasons I started to try and use this in my code, until I ran into the issue that prompted me to create this question.:-)
You will run into that same problem with other operators.
The only way to prevent this is to ensure that data types used in an expression always have the same data type, preferably by normalizing data types in your design, and if that is not possible by using explicit conversion. The implicit conversion rules are picky and can bite you.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply