Does a CASE statement execute all cases?

  • Hi,

    I've been having this discussion. See this example:

    DECLARE @i INTEGER = 1

    SELECT CASE @i

    WHEN 0 THEN

    (SELECT COUNT(1) FROM table1)

    ELSE

    (SELECT COUNT(1) FROM table2)

    END

    The outcome is the number of rows of table2.

    Question: when this query is executed, is also SELECT COUNT(1) FROM table1 executed? In other words: are both SELECT statements executed, or only the SELECT statement in the "true" clause like in this example SELECT COUNT(1) FROM table2 ?

  • the statement in the true clause will only

    executed

  • jeetsingh.cs (2/13/2013)


    the statement in the true clause will only

    executed

    And only the first one that evaluated to true

  • Thx!

  • i decided to do a test and view the execution plan.

    DECLARE @i INTEGER = 1

    SELECT CASE @i

    WHEN 0 THEN

    (SELECT COUNT(1) FROM table1)

    WHEN 2 THEN

    (SELECT COUNT(1) FROM table2)

    ELSE

    (SELECT COUNT(1) FROM table3)

    END;

    the execution plan shows:

    an Index Scan with Cost 4% on table1

    an Index Scan with cost 74% on table2

    an Index Scan with cost 2% on table3

    considering, in this example, it is Table3 that i'm getting the Count back from, and that has a cost of 2%, i'm presuming that SQL Server is doing something with the other tables to give Table1 & 2 a combined cost of 78%?

    or am i looking into the Execution Plan wrong?

  • Remember, SQL has to build a single plan ahead to time to cover any value. It doesn't look at the value of the variable when building the plan.

    But at run time it will only execute the first matching CASE statement.

    (At least typically: in certain cases, to gain speed, separate threads might run different CASE statements simultaneously, and discard those results that aren't actually needed. But that's due to hyper-threading, not the CASE statement itself.)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • The entire query is compiled when you execute it. But the path through the actual data will follow the CASE statement.

    But, this example shows the problem with using this approach. Let's say you have parameters that you're going to pass to these tables, different parameters for different tables with different values. When you pass a set of parameters, say for TableA, and null values for the other tables, the query plan compiles for the NULL values due to parameter sniffing. This can seriously impact performance when you then pass specific values for TableB, etc. If you're going to use this approach, I strongly recommend breaking out the queries into separate procedures so that your case statement determines which procedure to call. Then each procedure compiles on its own.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply