I understand the difference between these functions but my question is when checking for a single null value would ISNULL be any quicker than using COALESCE?
e.g
COALESCE(SELECT TOP 1 SomeValue FROM SomeTable, 0)
vs
ISNULL(SELECT TOP 1 SomeValue FROM SomeTable, 0)
Had a quick look into this as it's interesting to see a number of different comparisons out there on the performance between the 2. I think this blog post by Adam Machanic is most accurate in the performance benchmarking done on this topic, where the bottom line is:
... and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent
However, I share the same view as what he then goes on to say - that the difference is pretty negligible - e.g. in his tests, a million executions showed up on average a 0.7s difference. Is it worth it? I'd suggest there are probably bigger areas to optimise. But read the article, it's a good read.
