Comparison of ISNULL and COALESCE in TSQL

Check out the following sample code to understand the basic differences between ISNULL and COALESCE functions in SQL Server. Code is self explanatory and comments are provided for ease of understanding.

/****************************************************************************
 ISNULL function replaces NULL with the specified value. COALESCE function 
 returns the first non-NULL value among its arguments.
****************************************************************************/
DECLARE @var VARCHAR(20)
SELECT ISNULL(@var, 'Rami Vemula') AS 'ISNULL';
SELECT COALESCE(@var, NULL, 2) AS 'COALESCE';


/****************************************************************************
 ISNULL takes only 2 parameters whereas COALESCE takes variable number of 
 parameters.
****************************************************************************/
DECLARE @var1 INT
SELECT ISNULL(@var1, 2) AS 'ISNULL1';
SELECT COALESCE(@var1, NULL, 2) AS 'COALESCE1';


/****************************************************************************
ISNULL takes the data type length of the first argument, while COALESCE takes 
all parameters and uses max preceded parameters's length.
****************************************************************************/
DECLARE @var2 VARCHAR(4)
SELECT ISNULL(@var2, 'Rami Vemula') AS 'ISNULL2';
SELECT COALESCE(@var2, 'Rami Vemula') AS 'COALESCE2';


/****************************************************************************
ISNULL uses the first parameter data type. COALESCE uses highest precedence 
data type. ISNULL and COALESCE throws invalid cast exception when it encounters 
invalid casts because of precedence.
****************************************************************************/
DECLARE @var3 INT
SELECT ISNULL(@var3, 0) AS 'ISNULL3';
-- SELECT ISNULL(@var3, 'Rami Vemula') AS 'ISNULL'; -- Throw Error
SELECT COALESCE(@var3, 0, GETDATE()) AS 'COALESCE3';
-- SELECT COALESCE(@var3, 'Rami Vemula', GETDATE()) AS 'COALESCE'; -- Throw Error


/****************************************************************************
If the data types of both parameters are not determined in ISNULL, the data 
type returned is INT. In COALESCE at least one of the NULL values must be a 
typed NULL or else it will throw error.
****************************************************************************/
DECLARE @var4 INT
SELECT ISNULL(NULL, NULL) AS 'ISNULL4';
SELECT COALESCE(CAST(NULL as NVARCHAR), NULL) AS 'COALESCE4';
-- SELECT COALESCE(NULL, NULL) AS 'ISNULL'; -- Throw Error


/****************************************************************************
 ISNULL is an internal to TSQL Engine. COALESCE is an ANSI standard function.
****************************************************************************/

/****************************************************************************
When it comes to performance both ISNULL and COALESCE are same to a maximum
extent. But query execution plan differs for COALESCE because it will get
evaluated using a CASE statement. So one needs to be careful with the 
arguments of COALESCE.
****************************************************************************/

Output –