Performance difference for COALESCE versus ISNULL?

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?

• 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...

StackExchange Database Administrators Q#4274, answer score: 48