Differences between COALESCE & ISNULL

There is a lot of information about these statements.

I will try to summarise the info.You are welcome to add/correct

me in case I have missed something or not understood correctly.

---------------------------------------------------------------

1) Data type of returned expression:

The ISNULL will always return result set with

datatype equal to datatype of the first expression.

The returned value will be implicitly converted to the type

of the first value.In case the implicity conversion is impossible

you will get conversion error.

Data Type Precedence:

COALESCE will promote its arguments to the highest data type

in the expression list and it will be the returned datatype.

All expressions in COALESCE must be implicitly convertible.

DECLARE @str VARCHAR(3)
SELECT ISNULL(@str,'abcde') AS ISNULL_Result
SELECT COALESCE(@str,'abcde') AS COALESCE_result

-- Data Type precedence example:

SELECT 10/ISNULL(CONVERT(INT,NULL),3.0) AS IsnullResult
SELECT 10/COALESCE(CONVERT(INT,NULL),3.0) AS CoalesceResult

-- Another Data Type precedence example:

If the returned expression cannot be implicitly converted

to the data type with the higher precedence,an error will

be thrown:
See http://msdn.microsoft.com/en-us/library/ms190309.aspx

-

This statement will return 1 as the ResultSet
SELECT COALESCE(1,'abc') AS ResultSet

But the following statement will throw an error:

SELECT COALESCE('abc',1)

Conversion failed when converting the varchar

value 'abc' to data type int.

In the following statement you will get INCORRECT

result(due to Data Type precedence)

SELECT COALESCE(1,GetDate())

-- 1900-01-02 00:00:00.000

2)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.

For example,if you define computed column in a table

using COALESCE and make it Primary Key,you

will get the following error:

CREATE TABLE dbo.TestTable
(
col1 INT,
col2 AS COALESCE(col1,0) PRIMARY KEY
)

Cannot define PRIMARY KEY constraint on column

'col2' in table 'TestTable'. 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.

From BOL:

To index expressions involving COALESCE with

non-null parameters, the computed column can be persisted

using the PERSISTED column attribute(In this case SQL

Server will keep physically the data of computed column in

the table)

-- This will work

CREATE TABLE dbo.TestTable

(

col1 INT,

col2 AS COALESCE(col1,0) PERSISTED PRIMARY KEY

)

When using COALESCE at least one of the expressions

needs to have a type associated:

The following statement will thrown an error:

SELECT COALESCE(NULL,NULL)

because SQL Server is unable to infer a datatype for the

result from the input.The following will return NULL:

DECLARE @a INT,@b INT
SELECT COALESCE(@a,@b)

3)COALESCE is ANSI-92 compliant.It means when you take

your code and use it on another RDBMS, COALESCE will work.

ISNULL is a proprietary TSQL function

4)ISNULL takes only 2 parameters whereas COALESCE

takes variable number of parameters.In case you want

to use more than 2 expressions with ISNULL,you can

nest ISNULL expressions.

5)In some cases COALESCE can generate worst execution

plan than ISNULL ,so always check the execution plan.

Tweet