Coalesce vs Case

Question

I have 5 fields which are bringing back a mixture of values and NULLS. For reporting purposes I need to replace any potential NULLS with a value. The database that I am interrogating is updated nightly via an SSIS package.

Obviously I will need to alter the .dtsx file to stop NULLS being brought through each day by adding some SQL.

My question is:

What is the most efficient way of dealing with these NULLS in terms of performance. So far ive identified COALESCE and CASE to deal with them and im leaning towards COALESCE because my alternative to NULL is not going to change, but I would be interested to hear if and why this would be the most efficient method.

Solution 1

COALESCE() is literally shorthand for a CASE statement, they will perform identically.

However, as podiluska mentioned, ISNULL() can be occasionally faster than a CASE statement, but it's likely to be a miniscule increase as these functions are very unlikely to bottleneck your procedure.

Read here more about the performance differences.

Solution 2

In some circumstances, ISNULL is faster than CASE or COALESCE. However, if cross platform compatibility is an issue, COALESCE is ANSI standard.

Solution 3

I prefer to use COALESCE over CASE. and the most interesting is the code is much shorter than using CASE.