Performance difference for COALESCE versus ISNULL?

Question

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it. However, ISNULL seems easier to read since it seems more clear what it's doing.

I also realize that ISNULL is kind of tricky since it acts differently on different database servers and in different languages.

All of that, in my mind, boils down to style and standards. Given that style is subjective, is there any reason to use COALESCE over ISNULL (or vice versa)? Specifically, is there a performance advantage of one over the other?

Answer 1

COALESCE is internally translated to a CASE expression, ISNULL is an internal engine function.

COALESCE is an ANSI standard function, ISNULL is T-SQL.

Performance differences can and do arise when the choice influences the execution plan but the difference in the raw function speed is miniscule.

Answer 2

• ISNULL is Sybase/SQL Server specific
• COALESCE is portable

Then

• ISNULL take 2 arguments
• COALESCE takes 1-n arguments

Finally, and the fun bit. The result datatype and length/precision/scale

• ISNULL is the same as the first argument
• COALESCE is the highest according to datatype precedence

This last bit is why ISNULL is usually used because it's more predictable (?) and COALESCE can add unintended data type conversions: which is where the "it's slower" bit comes from

DECLARE @len10 varchar(10); --leave it NULL
SELECT
    ISNULL(@len10, '0123456789ABCDEF'),     -- gives 0123456789
    COALESCE(@len10, '0123456789ABCDEF');   -- gives 0123456789ABCDEF

All datatypes being the same, you won't see any practical difference...

Answer 3

As Mark pointed out, you're going to be hard-pressed to find performance differences; I think other factors will be more important. For me, I always use COALESCE, and most of this has already been mentioned by you or Mark:

• COALESCE is ANSI standard. It's one less thing I have to worry about if I'm going to port my code. For me personally this is not that important, because I know how infrequently such ports actually happen outside of Celko's classroom world, but to some people this is a benefit.
• Contrary to what you said about readability, I find it can be harder to read ISNULL especially for users coming from other languages or platforms where ISNULL returns a boolean (which doesn't exist in SQL Server). Granted, COALESCE is harder to spell, but at least it doesn't lead to incorrect assumptions.
• COALESCE is a lot more flexible, as I can say COALESCE(a,b,c,d) whereas with ISNULL I'd have to do a lot of nesting to achieve the same thing.

You should also be sure you are aware of how data type precedence is handled using the two functions if you are using it with different data types/precisions etc.

Note

There is one exception. These are handled differently in current versions of SQL Server:

SELECT COALESCE((SELECT some_aggregate_query),0); 

SELECT ISNULL((SELECT some_aggregate_query),0); 

The COALESCE variant will actually execute some_aggregate_query twice (once to check the value, and once to return it when non-zero), while ISNULL will only execute the subquery once. I talk about a few other differences here:

• Deciding between COALESCE and ISNULL in SQL Server