RSS Feed

Difference between isNull & COALESCE

Difference between isNull & COALESCE


Here are 3 points of difference between isNULL & Coalesce. Also below you can have a look at the table to get the points of difference between isNULL & 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 & COALESCE

[Via: Exam Ref 70-761 Querying Data with Transact-SQL by Itzik Ben-Gan]