Difference between COALESCE and ISNULL?

Question

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

If you avoid NULL values ​​in SQL concatenations, which one is best to use?

Thank!

Answer 1

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose, but can behave differently.

• Since ISNULL is a function, it is evaluated only once. As described above, the input values ​​for the COALESCE expression can be evaluated several times.
• The definition of the data type for the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the rules of the CASE expression and returns the data type of the value with the highest priority.
• The NULLability of the result expression is different for ISNULL and COALESCE. The return value of ISNULL is always considered NOT NULLable (provided that the return value is non-nullable), while COALESCE with non-zero parameters is considered NULL. Thus, the expressions ISNULL (NULL, 1) and COALESCE (NULL, 1), although the equivalent has different zero tolerance values. It matters if you use these expressions in computed columns, creating key constraints, or creating a scalar UDF return value 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 ); 

Checks for ISNULL and COALESCE are also different. For example, the NULL value for ISNULL is converted to int, whereas for COALESCE you must specify the data type. ISNULL accepts only 2 parameters, while COALESCE accepts a variable number of parameters.

Source: BOL

Answer 2

The main difference is that COALESCE is an ANSI standard, so you will also find it in other DBMSs, the other difference is that you can specify the complete list of values ​​to be checked on COALESCE , while you can pass ISNULL only one .

Answer 3

Since ISNULL is a function, it is evaluated only once. As described above, the input values ​​for the COALESCE expression can be evaluated several times. COALESCE basically converted to a CASE expression, and ISNULL is a built-in mechanism implemented in the database.

ISNULL faster than COALESCE .

MSDN

Answer 4

COALESCE() can have several inputs and will be evaluated in order until one of them is empty, for example COALESCE(Col1, Col2, Col3, 'N/A') . It is recommended that you use this MS instead of ISNULL()

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