COALESCE vs ISNULL and nested ISNULLs
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:
- As noted in the question, the two have different behaviours with respect to data types. Where this introduces implicit type conversions, cardinality estimation can be affected.
- COALESCE is expanded to a fixed form of CASE expression, which cannot be split into component parts or moved around the plan tree in the same way nested ISNULL expressions can. In short, ISNULL generally allows the optimizer more freedom. This extra freedom can result in better or worse plans, of course.
- SQL Server can reason about nullability with ISNULL but not so much with COALESCE. This can enable simplifications if an expression known not to be nullable (internally, during compilation) has this function applied. It can also affect cardinality estimation by removing null steps from intermediate histograms.
- Cardinality estimation for ISNULL may be performed using different logic than for CASE WHEN x IS NOT NULL THEN y ELSE …. More generally, there is no guarantee that logically equivalent predicates expressed using different syntax will produce the same estimation, or take the same path through optimization. In fact, most often they will not, especially in non-trivial cases. Small changes in CE can have large effects on final plan shape.
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.