Tuesday, July 7, 2009

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

No comments:

Post a Comment