This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:
an early part of our processing rewrites
COALESCE( expression1, expression2 )asCASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 )we generate:
SELECT CASE WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) ELSE 1 ENDLater 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
COALESCEtoISNULL, since the latter doesn’t duplicate the subquery.