A blog about SQL and MS SQL Server

One of the ways you can tell code that I have written from code that most other developers have written is that I use the ANSI92 compliant function COALESCE() instead of the more common proprietary ISNULL().

I do this for several reasons. First, COALESCE is not proprietary, and so it is highly unlikely that it will ever be dropped. Second, COALESCE is more flexible. In case you haven’t encountered these two functions, they have similar functionality.

ISNULL(arg1, arg2) returns the second argument if the first is NULL. If your database is rigidly designed and normalized to 3NF, you probably have little use for ISNULL and COALESCE. If, on the other hand, your database is like 99% of databases and it contains a mess of columns that allow NULL, you probably make extensive use of ISNULL.

COALESCE() takes a list of arguments of arbitrary length and returns the first non-NULL value it finds. This means that instead of long chains of ISNULL(ISNULL(ISNULL(col_a, col_b),col_c),col_d), you can write COALESCE (col_a, col_b, col_c, col_d). This is much easier to parse and leads to more logical, maintainable code.

This morning I encountered another reason to use COALESCE.

In my experience, genuine bugs (as opposed to behavior somebody doesn’t like) are rare in SQL Server. I don’t know whether this is a bug, or merely an allowed deficiency but:

I would expect this to return ‘1234567890′. However, it does not. On SQL Server 2005, this returns ‘12345′.

On the other hand,

returns, as expected, ‘1234567890′.

This is a subtle deficiency, one that could be difficult to track down, and is yet one more reason to use COALESCE.