Which is quicker COALESCE OR ISNULL ?

Question

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)

Answer 1

ISNULL will be faster i think because it has lesser function/code implementation for itself making it faster than COALESCE

Answer 2

ISNULL is a bit faster, even more on Sprocs. Some tests.

Edit: also, if you check STATISTICS TIME on the test above, ISNULL takes less CPU 20-30% less CPU to run.

Answer 3

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's. http://blog.falafel.com/2006/04/05/SQLServerArcanaISNULLVsCOALESCE.aspx

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

please check the Performance: ISNULL vs. COALESCE http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

Answer 4

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.