SQL - Difference between COALESCE and ISNULL?

What are the practical differences between COALESCE() and ISNULL(,'')?

When avoiding NULL values in SQL concatenations, which one is the best to be used?

Thanks!

> Comparing COALESCE and ISNULL > > The ISNULL function and the COALESCE expression have a similar purpose but can behave differently. > > 1. Because ISNULL is a function, it is evaluated only once. As described above, > the input values for the COALESCE expression can be evaluated multiple > times. > 2. Data type determination of the resulting expression is > different. ISNULL uses the data type of the first parameter, COALESCE > follows the CASE expression rules and returns the data type of value > with the highest precedence. > 3. The NULLability of the result expression is different for ISNULL and COALESCE. The > ISNULL return value is always considered NOT NULLable (assuming the return value is a > non-nullable one) whereas COALESCE with non-null parameters is > considered to be NULL. So the expressions ISNULL(NULL, 1) and > COALESCE(NULL, 1) although equivalent have different nullability > values. This makes a difference if you are using these expressions in > computed columns, creating key constraints or making the return value > of a scalar UDF deterministic so that it can be indexed as shown in > the following example.

> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), 
> col3 AS ISNULL(col1, 0) PRIMARY KEY  );

> Validations for ISNULL and > COALESCE are also different. For example, a NULL value for ISNULL is > converted to int whereas for COALESCE, you must provide a data type. > ISNULL takes only 2 parameters whereas COALESCE takes a variable > number of parameters.

Source: BOL

The main difference is, that COALESCE is ANSI-Standard, so you will also find it in other RDBMSs, the other difference is you can give a whole list of values to be checked to COALESCE whereas to ISNULL you can only pass one.

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.

MSDN

COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null such as COALESCE(Col1, Col2, Col3, 'N/A'). It's recommended to use this by MS instead of ISNULL()

ISNULL() can only have one input, however it's been shown to be slightly faster than COALESCE.