Posted by Erland Sommarskog on 10/02/62 11:45

Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
>
> Is there an argument for using Coalesce over IsNull?

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]