I know that multiple parameters can be passed to COALESCE, but when dealing with a specific condition, that is, when you want to to
check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL instead?
Is there any performance gain between the two?
This problem reported on MIcrosoft Connect reveals some differences between
an early part of our processing rewrites
COALESCE( expression1, expression2 )as
CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:
COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 )
SELECT CASE WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) ELSE 1 END
Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...
One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.