The plans attached (estimates only) show the difference of the same query with the exception that one uses COALESCE and the other is ISNULL. Much has been written about the difference before. COALESCE expands to a CASE expression and the datatype is determined by evaluating all expressions in the function. ISNULL doesn't behave that way. I get that part. I don't know how to use that knowledge and explain the difference in these plans.
By Dan Holmes 725 asked Jan 22, 2016 at 06:46 PM
The query in question is too complex, with too many unknown factors (view definitions, data types, column nullability and so on) to analyse in detail, but:
It is not sensible to say which will be better on average. Personally, I prefer to avoid both wherever possible. I also much prefer to write simpler queries with purely relational operators. In my experience, this leads to the best results from the optimizer, and the most predictable results.
The queries in the question are good examples of (estimated) low-cost queries where the optimizer will not spend much time (by design) trying to find anything better than a broadly reasonable solution. Most choices in this type of query are based on heuristics and guesses rather than detailed CE calculations anyway.
By SQLkiwi ♦ 6.6k answered Jan 23, 2016 at 11:48 AM