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:
SELECT ISNULL(‘abcd’ + null,‘1234567890′)
I would expect this to return ‘1234567890′. However, it does not. On SQL Server 2005, this returns ‘12345′.
On the other hand,
SELECT COALESCE(‘abcd’ + null,‘1234567890′)
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.
So True, So True
If Comedy is born of Pain, and Pain is born of Truth, then this post by the enigmatic Phil Factor should have you laughing through your tears. Figuratively speaking, of course. There’s no crying in software development.
If you’re one of those people who doesn’t like to click through without a taste of what is being clicked through to, Phil (not his real name, I hope) has broken down the phases of a software development project into the following: