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

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.

Answer 2

In this case, ISNULL is the best option. Because, SQL Server interprets COALESCE function as a CASE statement. So, your query

COALESCE(SELECT TOP 1 SomeValue FROM SomeTable, 0)

will be written by SQL Server as

1. CASE
2. WHEN (SELECT TOP 1 SomeValue FROM SomeTable) IS NOT NULL
3. THEN (SELECT TOP 1 SomeValue FROM SomeTable)
4. ELSE 0
5. END

If you observe the above interpretation, "SomeTable" will be scanned twice. But ISNULL will be evaluated only once.

Answer 3

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

Answer 4

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.

1. ISNULL Vs COALESCE

2. ISNULL vs COALESCE speed test

3. Please check the Performance: ISNULL vs. COALESCE

Answer 5

For what its worth You have a very specific use case so i used a sample of your actual question on the first in value on a table that came to mind and controlled the script for other variables. I assumed someval was an int as you used 0. My suggestion is that you choose your specific someval/sometable case and do the test yourself.

declare @val  int = 0;
declare @time1  Datetime2 = getdate();
declare @time2  Datetime2 = getdate();
Select @time1 = GETDATE();
while @MyCounter < 1000000
Begin
 Select @val = ISNULL((SELECT TOP 1 LocationID FROM location), 0)
 Select @MyCounter +=1;
END
Select @time2 = GETDATE();
Print datediff(millisecond,@time1,@time2);
Select @MyCounter = 0;
Select @time1 = GETDATE();
while @MyCounter < 1000000
Begin
 Select @val = COALESCE((SELECT TOP 1 LocationID FROM Location), 0)
 Select @MyCounter +=1;
END
Select @time2 = GETDATE();
Print datediff(millisecond,@time1,@time2);

The results were pretty dramatic, 11270 for isnull and 18930 for coalesce. Reversing the order of the loops as a second test produced 18260 for coalesce and 10810 for isnull. For your specific case I would say isnull is clearly faster.

This is not to say it is better in any other given situation. Using straight up values, or nvarchars or bits instead of int, or a column that is not a primary key, or Nesting isnull versus adding parameters to coalesce could change things.

This only addresses the question as it was asked.

Answer 6

I just ran a test on my own db. About 700k rows.

SELECT COUNT(*) FROM table WHERE COALESCE(field_1,field_2,field_3,field_4) IS NOT NULL

Result of 12106 obtained in 56 seconds.

SELECT COUNT(*) FROM table WHERE field_1 IS NOT NULL OR field_2 IS NOT NULL OR field_3 IS NOT NULL OR field_4 IS NOT NULL

Result of 12106 obtained in 0.00 seconds.