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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
3  
"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
up vote 18 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 '14 at 13:18
1  
@gotqn, You'd better make sure you got no MySQL devs then, Because iSnull is iFnull in MySQL. MySQL's iSnull means something else altogether. – Pacerier May 4 '15 at 10:30
  • 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

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:

share|improve this answer

COALESCE and ISNULL can impact performance when you are combining sets; for example, with joins or when you are filtering data. Consider an example where you have two tables T1 and T2 and you need to join them based on a match between T1.col1 and T2.col1. The attributes do allow NULLs. Normally, a comparison between two NULLs yields unknown, and this causes the row to be discarded. You want to treat two NULLs as equal. What some do in such a case is use COALESCE or ISNULL to substitute a NULL with a value that they know cannot appear in the data. For example, if the attributes are integers, and you know that you have only positive integers in your data (you can even have constraints that ensure this), you might try to use the predicate COALESCE(T1.col1, -1) = COALESCE(T2. col1, -1), or ISNULL(T1.col1, -1) = ISNULL(T2.col1, -1). The problem with this form is that, because you apply manipulation to the attributes you’re comparing, SQL Server will not rely on index ordering. This can result in not using available indexes efficiently. Instead, it is recommended to use the longer form: T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL), which SQL Server understands as just a comparison that considers NULLs as equal. With this form, SQL Server can efficiently use indexing.

share|improve this answer
2  
I feel that it is really difficult to read this huge block of text. You might consider improving its formating. As it is, it may be difficult to figure out if your answer adds something to already existing answers or at least it requieres a lot of motivation to read it to the end. See dba.stackexchange.com/help/formatting – Julien Vavasseur Dec 9 '15 at 16:39

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.