COALESCE and ISNULL in SQL Server

Introduction

COALESCE and ISNULL functions are used to return the first non-null expression among the input expression. Though these two functions look similar, there are certain differences. Let’s dive in to see the differences.

COALESCE and ISNULL

To understand the basic functioning of COALESCE and ISNULL, consider the following basic example:

In this case, both the functions return the same output of 1759. One advantage of COALESCE is that it supports multiple inputs.

Main differences include:

1. COALESCE is ANSI Standard whereas, ISNULL is SQL Server Specific
2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

Data Type Considerations

The main difference between COALESCE and ISNULL is their difference in handling various data types.

The data type of a COALESCE expression is the data type of the input with the highest data type precedence. If all inputs are un-typed NULLs, then an error will be returned.

The data type of an ISNULL expression is the data type of the first input. If the first input is an un-typed NULL literal, the data type of the result is the type of the second input. If both inputs are the un-typed literals, then type of the output is INT.

Output

Now, consider the example:

Output

Notice that with COALESCE, regardless of which input is specified first, the type of the output is VARCHAR (10) - the one with higher precedence. With ISNULL, the type of the output depends on the first input. If the first input is VARCHAR (3), then the result will also be VARCHAR(3).

Now consider the second example:

Output

As the INT data type has precedence over VARCHAR data type, SQL Server tries to convert the value ‘abc’ to an INT which results in error.

Performance Impact

Generally COALESCE (V1, V2) is internally translated by SQL Server as:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

For testing the performance of COALESCE and ISNULL, I am creating a new table called T1 and populating with sample data as below:

Consider the following query and include the actual execution plan:

It returns a sum of 124750 and returns an execution plan like this:

Though the table T1 is being referred only once, due to the COALESCE function, the table is scanned twice.

Now, rewrite the query as follows:

See the execution plan.

Note that this differentiation exists only in sub queries, while in normal queries both behave similarly.

Means:

Both the statements behave similarly with no performance differentiation.

Summary

In summary, please understand the following:

1. COALESCE and ISNULL are the two functions that will return a NON-NULL value instead of a NULL
2. The data type of the output returned by COALESCE will be the data type with highest precedence, whereas data type of the ISNULL output will be the data type of the first input.
3. COALESCE is ANSI-SQL Standard and can accept multiple parameters
4. As far as the performance of the query is concerned, ISNULL is the preferable choice in subqueries
5. I am going to write my second article on the same which will give you an insight into the impact of COALESCE and ISNULL in transactions.