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.
ISNULL will be faster i think because it has lesser function/code implementation for itself making it faster than COALESCE
Please check the link
to prefer ISNULL over COALESCE when given the choice is that ISNULL tends to produce query plans that are more efficient than COALESCE.
Please check the Performance: ISNULL vs. COALESCE