Difference between isNull and COALESCE
Here are 3 points of difference between isNULL and Coalesce. Also below you can have a look at the table to get the points of difference between isNULL and Coalesce at a glance.
1. The type of the COALESCE expression is the type of the input expression with the highest precedence, whereas the type of the ISNULL expression is determined by the first input. [Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, Ron Talmage]
The type returned from ISNULL changes depending on the order of the input parameters, while COALESCE returns the data type of the highest precedence regardless of the argument order. [SQL Server T-SQL Recipes by David Dye, Jason Brimhall, Timothy Roberts, Wayne Sheffield, Joseph Sack, Jonathan Gennick]
Declare @my_new_query NVARCHAR(MAX) = '
Declare
@x AS decimal(3,2) = 2,
@y AS int = 3;
SELECT ISNULL(@x,@y) as ISNULL_with_first_input_as_decimal,
ISNULL(@y,@x) as ISNULL_with_first_input_as_int,
COALESCE(@x,@y) as COALESCE1,
COALESCE(@y,@x) as COALESCE2;';
EXEC sp_executesql @my_new_query;
SELECT column_ordinal, is_nullable, system_type_name
from master.sys.dm_exec_describe_first_result_set(@my_new_query, NULL, 0) a ;
| ISNULL_with_first_input_as_decimal | ISNULL_with_first_input_as_int | COALESCE1 | COALESCE2 |
|---|---|---|---|
| 2.00 | 3 | 2.00 | 3.00 |
| column_ordinal | is_nullable | system_type_name |
|---|---|---|
| 1 | 1 | decimal(3,2) |
| 2 | 1 | int |
| 3 | 1 | decimal(12,2) |
| 4 | 1 | decimal(12,2) |
From within decimal and int, decimal has higher precedence.
2. 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.
SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)
SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as Decimal), NULL);', NULL,0)
| (No column name) | name | system_type_name |
|---|---|---|
| ISNULL | NULL | int |
| (No column name) | name | system_type_name |
|---|---|---|
| COALESCE | NULL | decimal(18,0) |
[Via]
dm_exec_describe_first_result_set is dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
3. ISNULL takes 2 parameters only but COALESCE takes a variable number of parameters.
You can also look at the below table which clearly depicts the difference between isNull and COALESCE
[Via: Exam Ref 70-761 Querying Data with Transact-SQL by Itzik Ben-Gan]