Someone recently asked me something like “Is ISNULL or COALESCE better to use?”.
There is semantically very little difference. COALESCE is ANSI-standard and ISNULL is Microsoft implementation.
Both returns the first non-null value. ISNULL accepts a total of 2 parameters and COALESCE accepts a total of at least 256 parameters.
However, there is a difference in the result!
ISNULL uses the datatype from the first parameter and returns a result with the same datatype.
COALESCE uses data type precedence and uses the datatype with the highest precedence.
Why is this important? You can create a bug when using ISNULL or even crash your application!
Wait until last example.
Consider this sample setup.
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic1.png)
Now let’s see what the result and datatypes are.
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic2.png)
As you can see, you will get the expected result. What about the datatypes used?
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic3.png)
ISNULL uses the datatype for the first parameter and COALESCE uses the datatype with highest precedence.
“Where is the bug?” you ask. I’ll show you.
Let @i be NULL in the first sample data.
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic4.png)
Whoopsie. Now you made your application crash!
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic5.png)
Let use remove all columns using variable @s.
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic6.png)
And there is the bug. Since @i is NULL, the function ISNULL returns the second parameter but still using the datatype for the first parameter.
Whoops.
What happens if we use COALESCE instead?
No crash. Correct result.
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic7.png)
On the other hand, we can use COALESCE and still crash your application even if the first parameter is not null!
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic8.png)
![](https://www.sqltopia.com/wp-content/uploads/2022/02/pic9.png)