Efficiency of using CASE WHEN ... IS NOT NULL vs ISNULL/COALESCE

Consider the following scenario:

• There are three kinds of entities, say Foo, Bar and Baz.
• Every Foo must be associated with a Bar or a Baz, but not both at the same time.

The scenario is already implemented in the following way:

• There are three tables: Foo, Bar and Baz.
• Foo has two foreign key fields: Bar_ID and Baz_ID.
• Exactly one of those foreign key fields must be NULL.

Now I would like to build a query displaying the list of Foos, including the description of the Bar or Baz each Foo is associated to. Actually, the description of a Bar is a quite complicat开发者_运维知识库ed formula of the fields of the corresponding row in the Bar table. The same applies to Baz.

My current query looks like the following:

SELECT    Foo.*,
          CASE
            WHEN Foo.Bar_ID IS NOT NULL THEN
              -- a formula, say...
              ISNULL(Bar.LotNumber + '-', '') + Bar.ItemNumber
            WHEN Foo.Baz_ID IS NOT NULL THEN
              -- another formula, say...
              ISNULL(Baz.Color + ' ', '') + Baz.Type
          END AS 'Ba?Description'
FROM      Foo
LEFT JOIN Bar ON Bar.Bar_ID = Foo.Bar_ID
LEFT JOIN Baz ON Baz.Baz_ID = Foo.Baz_ID

Is the preceding query is more, less or equally efficient than...

SELECT    Foo.*,
          ISNULL( -- or COALESCE
            ISNULL(Bar.LotNumber + '-', '') + Bar.ItemNumber,
            ISNULL(Baz.Color     + ' ', '') + Baz.Type
          ) AS 'Ba?Description'
FROM      Foo
LEFT JOIN Bar ON Bar.Bar_ID = Foo.Bar_ID
LEFT JOIN Baz ON Baz.Baz_ID = Foo.Baz_ID

...?

---

In theory, the CASE shlould be because only one expression is evaluated. Several chained ISNULLs will all require processing.

However, you'd need to have a large (10000s of rows) dataset to notice any difference: most processing goes into the actual table access, JOINs etc.

Have you tried it? You can use SQL profiler to see CPU etc for each query.

---

I believe that ISNULL is more efficient. CASE statements are always evaluated first and I believe restrict the options available to the query optimiser.

What does the execution plan say? Is that your actual question?

---

COALESCE is an ANSI function while ISNULL is an SQL Server proprietary function.

They differ in type handling and some other things, and COALESCE may accept more than two arguments.

For your task (two arguments, both VARCHAR) they are equivalent.