ISNULL or COALESCE? Sometimes it Really Does Matter

After seeing some poor performance in a query that I couldn't figure out, I ended up reading a lot of discussions about the "ISNULL / COALESCE" debate. Some people feel one is faster than the other, and some people like COALESCE because it's ANSI standard. (And they hold these opinions very strongly.)

I always thought the two were identical (when just two values are involved, of course), but it turns out that they have a difference that can affect performance. The value that ISNULL returns is typed to be same as the type of the first argument. The value that COALESCE returns is typed to be the same as the argument with the highest data type precedence.

Adam Machanic says: "What does this have to do with query performance? Sometimes, when using ISNULL or COALESCE as part of a predicate, a user may end up with a data type mismatch that is not implicitly convertable and which therefore causes a table scan or other less-than-ideal access method to be used."

Data type mismatches can cause table scans? I had totally forgotten about that. Makes sense though: if there's no implicit conversion, you'll have to use a CAST or CONVERT, which means each row will have to be evaluated.

That's what was causing the poor performance I was seeing. I wouldn't advocate a global search-and-replace, but I will be looking more closely at the COALESCE statements I come across.