ISNULL vs COALESCE – expressions/functions in SQL Server

ISNULL & COALESCE with some common features makes them equivalent, but some features makes them work and behave differently, shown below.

– Similarity
Both can be use to build/create a CSV list as shown below:

USE [AdventureWorks]
GO

DECLARE @csv VARCHAR(2000)

SELECT @csv = ISNULL(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

set @csv=NULL
SELECT @csv = COALESCE(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

Both will give the same output:
Carla, Catherine, Frances, Gustavo, Humberto, Jay, Kim, Margaret, Pilar, Ronald

– Difference #1
ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter, example below.

DECLARE @str1 VARCHAR(10), @str2 VARCHAR(10)

-- ISNULL() takes only 2 arguments
SELECT ISNULL(@str1, 'manoj') AS 'IS_NULL' -- manoj

-- COALESCE takes multiple arguments and returns first non-NULL argument
SELECT COALESCE(@str1, @str2, 'manoj') AS 'COALESCE' -- manoj

-- ISNULL() equivalent of COALESCE, by nesting of ISNULL()
SELECT ISNULL(@str1, ISNULL(@str2, 'manoj')) AS 'IS_NULL eqv' -- manoj

– Difference #2
ISNULL does not implicitly converts the datatype if both parameters datatype are different.
On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.

-- ISNULL Does not do Implicit conversion
select ISNULL(10, getdate()) as 'IS_NULL' -- Errors out

Error Message:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed.
Use the CONVERT function to run this query.

-- COALESCE Does Implicit conversion and gets converted to higher precedence datatype.
select COALESCE(10, getdate()) as 'COALESCE' -- 1900-01-11 00:00:00.000, outputs 10 but convert it to datetime [datetime > int]
select COALESCE(getdate(),10) as 'COALESCE' -- {Current date} 2010-12-23 23:36:31.110
select COALESCE(10, 'Manoj') as 'COALESCE' -- 10 [int > varchar]
select COALESCE('Manoj',10) as 'COALESCE' -- Errors out, it does an implicit conversion, but cannot change 'Manoj' to Integer.

Error Message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Manoj' to data type int.

– Difference #3
Similar to above point ISNULL always returns the value with datatype of first parameter.
Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.

DECLARE @str VARCHAR(5)

SET @str = NULL

-- ISNULL returns truncated value after its fixed size, here 5
SELECT ISNULL(@str, 'Half Full') AS 'IS_NULL' -- Half

-- COALESCE returns full length value, returns full 12 char string
SELECT COALESCE(@str, 'Half Full') AS 'COALESCE' -- Half Full

– Difference #4
According to MS BOL, ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. Thus to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute.

-- ISNULL() is allowed in computed columns with Primary Key
CREATE TABLE T1 (
	col1 INT,
	col2 AS ISNULL(col1, 1) PRIMARY KEY)

-- COALESCE() is not allowed in non-persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PRIMARY KEY)

Error Message:
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T2'. 
The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

-- COALESCE() is only allowed as persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PERSISTED PRIMARY KEY)

-- Clean up
DROP TABLE T1
DROP TABLE T2

MSDN BOL links:
ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
COALESCE: http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE