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