Fal (3/30/2010)

---

This QotD raises a point I'm always having to remind developers about, and I guess SQL guys can also do with a reminder from time to time.

However, my 2 cents is rather than...
declare @s char(4)
select ISNULL(convert(varchar(16),@s),'Unknown')

...I would use...
declare @s char(4)
select coalesce(@s,'Unknown')

...which also has the ability to take more than 2 arguments.

S.

richard.maw (3/30/2010)

---

Can anyone give an account of why the semantics of isnull and coalesce (with two args) is different?

COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.
...
COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function

CirquedeSQLeil (3/30/2010)

---

However, you will find that many prefer the isnull due to an increase in performance.

James Lean (3/31/2010)

---

Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?

Hugo Kornelis (3/31/2010)

---

So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END.

SELECT  CASE CHECKSUM(NEWID()) % 3 + 1
        WHEN 1 THEN 'One'
        WHEN 2 THEN 'Two'
        WHEN 3 THEN 'Three'
        ELSE 'WTF?'
        END

And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.