Excellent article, Michael.

There are a couple of other trade-offs to consider when deciding whether to use COALESCE() or ISNULL(). I'll leave it to you to decide which are the pro's and con's of each

In the following repro script:

CREATE TABLE dbo.MyTable(

  i int NOT NULL PRIMARY KEY,

  j int NULL

)

GO

INSERT INTO dbo.MyTable(i, j)

SELECT 1, 100 UNION

SELECT 2, 200 UNION

SELECT 3, NULL

GO

SELECT COALESCE(j, 'No Name Given')

FROM dbo.MyTable

SELECT ISNULL(j, 'No Name Given')

FROM dbo.MyTable

GO

DROP TABLE dbo.MyTable

We see that COALESCE() has weaker type checking than ISNULL, as it allows the selection of the 'j' column where the value is non-NULL. ISNULL(), on the other hand, rejects the statement from the outset. Score one for ISNULL(), in my opinion.

But COALESCE() allows us to do this:

DECLARE @MyVar1 int, @MyVar2 int, @MyVar3 int

SET @MyVar1 = NULL

SET @MyVar2 = NULL

SET @MyVar3 = 3

SELECT COALESCE(@MyVar1, @MyVar2, @MyVar3)

So COALESCE() allows "n" number of expressions to be listed. Score one for COALESCE().

This last one will getcha for sure if you're not careful:

DECLARE @MyVC1 varchar(2), @MyVC2 varchar(4)

SET @MyVC1 = NULL

SET @MyVC2 = 'abcd'

SELECT COALESCE(@MyVC1, @MyVC2)

SELECT ISNULL(@MyVC1, @MyVC2)

This seems to be related to the "weaker" type-checking that I pointed out earlier. COALESCE() is a bit looser with the implicit conversions, where ISNULL() will always convert to the datatype of the first argument. In this case, COALESCE() seems to be the better choice (trust me, I spent a half hour helping a developer track down what was going on with an ISNULL() construct once), but it may be only masking some sloppy coding which would come back to bite you at a later time.

HTH,

TroyK