CODEWITHSUNDEEP
sql-server
isekaijin
isekaijin

Reputation: 19772

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

Consider the following scenario:

The scenario is already implemented in the following way:

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

...?

Upvotes: 6

Views: 27623

Answers (3)

Quassnoi
Quassnoi

Reputation: 425823

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.

Upvotes: 1

Joel Mansford
Joel Mansford

Reputation: 1316

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?

Upvotes: 1

gbn
gbn

Reputation: 432672

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.

Upvotes: 5

Related Questions