0

I have seen in some projects to use COALESCE like this:

COALESCE( [MaterialType], 'ROH')

This expression COALESCE([field], constant) is equivalent to use the ISNULL() function, except for the nullability of both expressions.

My question is:

Is the performance of COALESCE([field], constant) better than ISNULL()?

Thank you

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Sergio Prats
  • 1,043
  • 1
  • 14
  • 19
  • 3
    http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx (isNull faster barely) Now note: Coalesce will allow N values to be compared while isNull is limited to just the 2 unless you nest functions. I personally stick with coalesce as it's more RDBMS independent; and I'll take a slight performance hit for that and flexibility of not nesting nulls. Also note the resulting datatype may vary: isNull takes the type of first input, coalesce looks at all entries and usees the type of highest precedent. http://sqlmag.com/t-sql/coalesce-vs-isnull – xQbert Sep 19 '17 at 17:30
  • 1
    While these two are often used interchangeability they are different. But based on This specific usecase, isNull should function slightly faster based on adam_macanic's article. Should be noted that the SQL server version makes a difference. and in comments adam illustrates coalesce is faster in 2008. long story short: performance tune later (if needed); get correct results first and you'll likely find other areas to tune to get better performance first. – xQbert Sep 19 '17 at 17:33
  • So Both functions have a quite similar performance but COALESCE is compatible with more RDBMS. – Sergio Prats Sep 20 '17 at 08:25

0 Answers0