Consider this SQL Server statement:
SELECT ISNULL(2, 2.0), COALESCE(2, 2.0)You might assume, as I did, that they're equivalent and will produce the same result, however this is not the case.
They differ in the way they determine what data type to return. ISNULL always returns the data type of the first argument and the second argument must implicitly cast to that type. COALESCE on the other hand returns the data type of the argument with highest data type precedence.
As a result what you get out of the example above is:
| 2 | 2.0 |
The reason being that the constant "2" is treated as an integer while "2.0" is treated as a decimal. Therefore ISNULL will return an integer because the first argument is an integer but COALESCE returns a decimal because decimal has higher precedence.
One possible issue I can envisage here is if you were to inadvertently COALESCE a float column in with your decimals and then try to do some arithmetic. You'd end up doing dodgy float arithmetic where you thought you were doing safe decimal arithmetic.
1 comment:
Your example can be misleading as the result returned from COALESCE is the same value as the second argument - 2.0
Instead, using two different values may be clearer.
For example, COALESCE(1, 2.0)
Post a Comment