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

Question

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 complicated 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

...?

Answer 1

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.

Answer 2

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?