Coalesce vs Case

Question

I have 5 fields that return a mixture of values ​​and NULLS. For reporting purposes, I need to replace any potential NULLS with a value. The interrogation database I update every night through the SSIS package.

Obviously, I will need to modify the .dtsx file to stop the NULLS going through every day by adding some SQL.

My question is:

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

Answer 1

COALESCE() literally abbreviated for the CASE statement, they will execute the same way.

However, as mentioned in podiluska, ISNULL() can sometimes be faster than the CASE statement, but it is likely to be a slight increase, as these functions are unlikely to limit your procedure.

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

Answer 2

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

Answer 3

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