Why SQL Server Sucks: ISNULL

Question: Why does SQL Anywhere have both ISNULL() and COALESCE()?

They both work exactly the same way, they both perform a useful function (return the first non-null argument), and they both have stupid names, so why the duplication?

Why not have just one function, with a decent name, like FIRST_NON_NULL()?

Answer: Here's a guess: It might have something to do with "Transact SQL compatibility" which is a historical requirement that by its very nature breaks the fundamental rule "WATCOM does things the way they should be done."

In this case, however, "compatibility" is not absolute: In Microsoft SQL Server the two functions do not work exactly the same way. For one thing, Transact SQL ISNULL() accepts only two arguments whereas Transact SQL COALESCE() accepts two or more.

But it gets worse...

When calling ISNULL() in Microsoft SQL Server the second argument "must be of a type that is implicitly convertible to the type" of the first argument.

That sounds OK, until you think about it.

Even after you think about it, it probably still sounds OK... ISNULL() could return either argument so they have to have compatible types, right?

It sounds OK until you actually run a test using Microsoft SQL Server 2008:

1> BEGIN
2>    DECLARE @hello VARCHAR ( 10 );
3>    SET @hello = NULL;
4>    SELECT ISNULL ( @hello, 'Hello, World!' ) AS Result;
5> END
6> GO
 Result
 ----------
 Hello, Wor

(1 row affected)

Go ahead, justify that behavior... the inventors of SQL Anywhere clearly couldn't, which is probably why they didn't try.

In SQL Anywhere "the way things should be done" trumps "Transact SQL compatibility" and here's a test using SQL Anywhere 12 to prove it:

BEGIN
   DECLARE @hello VARCHAR ( 10 );
   SET @hello = NULL;
   SELECT ISNULL ( @hello, 'Hello, World!' ) AS Result;
END
GO

Result
'Hello, World!'

For the record, SQL Server and SQL Anywhere both return 'Hello, World!' when you call COALESCE() instead of ISNULL().

...and for a longer discussion of ISNULL's strange behavior in SQL Server see Twist in ISNULL function.

---