COALESCE and ISNULL Which is Better in MS SQL Server

In many Interview I have faced this question. You can consider this as a common question for every Interview (.Net with MS SQL Server). There are many questions COALESCE and ISNULL, but answer for all questions are same. Some one will ask "instead of using NULL which other function you will use for same purpose". If you will answer this question then there is another question "Why? why you will use COALESCE instead of ISNULL?"

So lets discuss the main difference and pros and cons of using COALESCE and ISNULL in MS SQL SERVER.
As per MSDN we have the following:
COALESCE Returns the first non null expression among its arguments and ISNULL Replaces NULL with the specified replacement value.
So we can say that both do the almost same work but still there are some difference in their working property. COALESCE takes multiple parameters where as ISNULL takes only 2 parameters. 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. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:

CREATE TABLE #CheckSumTest 
    (
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
    );

The basic syntax for both the functions are given below.
CAOLESCE

COALESCE ( expression [ ,...n ] ) 

ISNULL

ISNULL ( check_expression , replacement_value )

Lets figure out the property of each functions. begin with COALESCE

COALESCE

1. Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
2. If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.
3. COALESCE(expression1,...n) is equivalent to the following CASE expression:

   CASE
   
      WHEN (expression1 IS NOT NULL) THEN expression1
   
      WHEN (expression2 IS NOT NULL) THEN expression2
   
      ...
   
      ELSE expressionN
   
   END 
   

ISNULL

1. From the syntax of the ISNULL, It returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.
2. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
3. Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL. See following example

   USE AdventureWorks2012;
   GO
   SELECT Name, Weight
   FROM Production.Product
   WHERE Weight IS NULL;
   GO
   

COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function

Performance Comparison

You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and queries with COALESCE often fare worse here. See below script:

use tempdb
go
create table table1 (Col1 int );
create table table2 (Col1 int );
create table table3 (Col1 int );
go
set showplan_text on;
go
select isnull((select Col1 from table1 t1 where t1.Col1 = t2.Col1), (select max(Col1) from table3 t3))
from table2 t2;

select coalesce((select Col1 from table1 t1 where t1.Col1 = t2.Col1), (select max(Col1) from
table3 t3))
from table2 t2;
go
set showplan_text off;
go
drop table table1, table2,table3;
go

Below figure(Courtesy  : mssqltips.com) provides performance comparison between both ISNULL and COALESCE

For more details please refer Deciding between COALESCE and ISNULL in SQL Server Article on MSSQLTIPS.COM , Its is explained in very detail and can be understand easily.
Thanks for reading articles on my blog. Please post  your questions if any as a comment, I will be delighted to answer your queries.

Happy Koding



Regards
Dharmendra Kumar