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.

I think the answer will be "it depends". Test out both with your systems and data, and find out for yourself! If both give relatively the same performance, go with the one you and your team feel most comfortable with. — Bridge, Nov 01 '12 at 10:20

score 21 · Accepted Answer · edited Oct 29 '20 at 07:59

21

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.

edited Oct 29 '20 at 07:59

Jacob van Lingen

8,989
7
48
78

answered Nov 01 '12 at 14:44

mrmillsy

495
3
14

podiluska · Answer 2 · 2013-12-09T17:15:43.083

7

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

edited Dec 09 '13 at 17:15

answered Nov 01 '12 at 09:55

podiluska

50,950
7
98
104

John Woo · Answer 3 · 2012-11-01T14:25:23.303

6

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

edited Nov 01 '12 at 14:25

answered Nov 01 '12 at 09:45

John Woo

258,903
69
498
492

Coalesce vs Case

3 Answers3

Linked