simple case vs. searched case

Question

simple case vs. searched case

btio_3000

SSCrazy

Points: 2662
More actions
April 25, 2011 at 5:22 pm

#249335

Hi,
Does it matter if I use simple case, i.e.
SELECT CASE STATUS WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 0 END
FROM Table1 t1
INNER JOIN Table 2 t2
ON CASE t1.STATUS WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 0 END = t2.STATUS
or searched case, i.e.
SELECT CASE WHEN STATUS='A' THEN 1 WHEN STATUS='B' THEN 2 ELSE 0 END
FROM Table1 t1
INNER JOIN Table 2 t2
ON CASE WHEN t1.STATUS='A' THEN 1 WHEN t1.STATUS='B' THEN 2 ELSE 0 END = t2.STATUS
in a matter of speed?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Why don't you run some tests and find out (and then tell us what you find)??

In any case, I imagine that your query will be overwhelming compared to any possible difference between the scenarios you posit. That does assume that the present the same query plan, btw. Do they?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

btio_3000 SSCrazy Points: 2662 More actions · Answer 2

Hi Kevin,

I do not currently have permissions to look at the exec plan, and using searched seems to be faster - just a bit but consistently - than simple one (it reads through a million of recs returning about 700,000, no indexes on tables, with a difference of about 7 sec every time)

Which seems to be a little strange, that's basically the reason I am asking

Thanks!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Jeff Moden posted an elegant proof not so long ago that simple CASE is converted internally to searched CASE, I'll see if I can find it.

http://www.sqlservercentral.com/Forums/FindPost989878.aspx

Edit: added link

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

btio_3000 SSCrazy Points: 2662 More actions · Answer 4

That would explain it... From that post it seems to me though, Jeff was rather llustrating use of a non-deterministic function in CASE...