Why I Prefer COALESCE over ISNULL

I did a bit of research on the matter and have found that, due to the differences in the way that the two functions deal with Data Types and Data Lengths, COALESCE is the favoured function.


These T-SQL statements helped paint the picture:


SELECT ISNULL(CAST(NULL AS INT), 5.5) -- Returns 5
SELECT COALESCE(CAST(NULL AS INT), 5.5) -- Returns 5.5
SELECT DATALENGTH(ISNULL(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 5
SELECT DATALENGTH(COALESCE(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 10


As well as:


SELECT
Nullif('abc', 'abc') AS the_null,
ISNULL(Nullif('abc', 'abc'), '123456') AS using_isnull,
COALESCE(Nullif('abc', 'abc'), '123456') AS using_coalesce,
ISNULL(Nullif('abc', 'abc'), 123456) AS int_using_isnull,
COALESCE(Nullif('abc', 'abc'), 123456) AS int_using_coalesce


These were sourced from this great post about the matter:

Best Practice: Coalesce VS IsNull