Does a CASE statement execute all cases?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Does a CASE statement execute all cases?

Does a CASE statement execute all cases?

Rate Topic

Display Mode

Topic Options

Author

Message

Raymond van Laake

Posted Wednesday, February 13, 2013 5:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 100, Visits: 271

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 ?

Post #1419431

jeetsingh.cs

Posted Wednesday, February 13, 2013 5:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380

the statement in the true clause will only
executed

Post #1419445

e4d4

Posted Wednesday, February 13, 2013 7:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 151, Visits: 1,033

jeetsingh.cs (2/13/2013)

the statement in the true clause will only
executed

And only the first one that evaluated to true

Post #1419517

Raymond van Laake

Posted Wednesday, February 13, 2013 7:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 100, Visits: 271

Thx!

Post #1419532

davidandrews13

Posted Wednesday, February 13, 2013 8:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 456, Visits: 2,672

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?

Post #1419547

ScottPletcher

Posted Wednesday, February 13, 2013 10:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1419649

Grant Fritchey

Posted Thursday, February 14, 2013 5:11 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1419992

« Prev Topic | Next Topic »

Permissions