Menu
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.
625
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.
Compare COALESCE and CASE According to Microsoft Documents "The COALESCE expression is a syntactic shortcut for the CASE expression." It goes on to say that the COALESCE expression is rewritten by the query optimizer as a CASE expression.
The SQL COALESCE function can be syntactically represented using the CASE expression. For example, as we know, the Coalesce function returns the first non-NULL values. SELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME; The above Coalesce SQL statement can be rewritten using the CASE statement.
With ISNULL(), you can only provide one alternate value but with COALESCE you can provide more than one e.g. if col1 IS NULL then take value from column2, if that is NULL then take the default value.
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.
153
In some circumstances, ISNULL is faster than CASE or COALESCE. However, if cross platform compatibility is an issue, COALESCE is ANSI standard.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
