Difference between ISNULL and COALESCE in T-SQL

Question

I read the comparison between ISNULL and COALESCE on msdn.
Microsoft's explain

The part that

"Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type."

confused me. Someone could give me an example to make it more clear?

Thanks for help

Answer 1

It is valid query and will return INT:

SELECT ISNULL(NULL, NULL);

It will fail:

SELECT COALESCE(NULL, NULL);

At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

It is valid query and will return INT:

 SELECT COALESCE(CAST(NULL AS INT), NULL);

You can check metadata for resultsets:

DEMO

SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)
UNION ALL
SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as FLOAT), NULL);', NULL,0)