Oh, very well done. This is actually some very important information because, in the name of portability (and true portability is still a myth if you do anything really practical), a lot of people lean toward using COALESCE for two part implicit ISNULLs without understanding the differences.
There are a couple of other differences that people should be made aware of, as well...
ISNULL will always try to implicitly convert the datatype of the 2nd operand to the datatype of the first while COALESCE will return the highest precedence of all the datatypes in all of the operands of expression. That's super important if you're using COALESCE in the process of building a Temp Table on-the-fly with SELECT/INTO because it could change the datatype of the resulting column in the Temp Table to something you weren't expecting and query predicates against that column may cause implicit conversions making it impossible to do index seeks. The same holds true for computed columns and it can really make a tuning mess unless you're careful to use the correct data type in all the operands of expression.
Behind the scenes, COALESCE is more complex at the machine language level and, when used across hundreds of thousands of rows, is actually a tiny bit slower than ISNULL.
commented on Oct 5 2011 6:09AM