Colasce and ISNULL

Colaesce
Coalesce and ISNULL are SQL server system functions. Both are using to check the null values in the parameters.
However there are few differences as follows.
1. Coalesce requires atleast two parameters. Can have n number of parameters.
2. Coalesce is an ANSI standard
3. COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes
4. Caution using coalesce in sub query; it may create less efficient execution plan.

ISNULL
1. ISNULL is not a NASI standard. It has been opted by Microsoft in addition to ANSI. ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs
2. The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL).
3. ISNULL uses an implicit conversion of data type to the first parameter. Beware of these kind of conversions in real time.

Examples of colaesce usages :

1. The following query can be improved as follows:
Where ( DBA_StartdateIs NULL Or DBA_Startdate <= @Startdate) Use the below: Where Colaesce(DBA_Startdate, @Startdate) <= @Startdate

2. The following query uses seeks If you wanna check the parameters upon the availability, coalesce/ISNULL can be given as follows to enforce the seeks on the index.

SELECT * FROM Authors WHERE AU_LName LIKE COALESCE(@lastName, '%')

3. Comma separated string can be created using Colaesce instead of using Cursors

Declare @TableList Varchar(8000) 

Select @TableList = COALESCE(@TableList + ', ', '') +Cast(A.TableName As varchar(50))From Table1 A 

Left Join Table2 B On A.TableName = B.TableName 

And Convert(Varchar(50),Getdate(),101)= Convert(Varchar(50),B.Date_Column,101)

Where (B.TableName Is Null)

Case Study:
Createdtwo table T and T2 as the below specifications:/>

The below execution plan is for the coalesce usage. The plan does not have any compute scalara operator as it happens with the ISNULL.
The below figure is XML plan for ISNULL, where an implicit conversion happens for all cases. It does not really not based on any CASE as it happens with the COALESCE.


The below execution plan is with ISNULL function, where it is clearly showing that an operator in the execution.

The XML plan of the query is much more detailed and much more reliable. The below XML plan for the coalesce shows a CASE statement inside an implicit conversion to make the result as discussed above for precendence and accommodating all possible outcomes.

From all the ablove, it always better to look at the XMLplan than graphical representation. Even COALESCE has an implicit conversion of datatype within the CASE, it does not show any indication in the graphical representation of the execution plan.

In real time scenarios, lets really evaluate the possible better way between ISNULL and COALESCE for the optimized code. Need to take a call based on your scenario.There is no hard rule found in this case.

Happy Coding!!!