Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I don't see mentioned in any of the answers that a sub query in a COALESCE gets evaluated twice. –  Martin Smith Apr 18 '12 at 6:56
2  
"ISNULL seems easier to read since it seems more clear what it's doing" -- really? I find the name counter-unintuitive: I would expect it to return a Boolean indicating whether an expression resolved to null or unknown. The name COALESCE is merely unintuitive ;) –  onedaywhen Apr 18 '12 at 7:20
add comment

3 Answers

up vote 15 down vote accepted

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.

share|improve this answer
    
So, in cases where the both functions can do the same thing like COALESCE(@VAR,'') and ISNULL(@VAR,''), knowing that ISNULL is internal engine function means we should use it? To be more accurate, talking about SQL Server 2012. –  gotqn Jan 9 at 13:18
add comment

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.

share|improve this answer
3  
+1 for "outside Celko's classroom" :) –  Mark Storey-Smith Aug 4 '11 at 1:53
add comment
  • 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...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.