concatenating CASE stmt results

Question

Post reply

concatenating CASE stmt results

Viewing 3 posts - 16 through 17 (of 17 total)

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 1

Hmm, interesting. I guess so.

But I don't see how could it be evaluated only once.

I mean, how could SQL determine equality vs 5 (10, 20, 50, whatever) values with one comparison?

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1004475 More actions · Answer 2

Chris Morris-439714 (9/21/2010)
drew.allen (9/21/2010)
Jeff Moden (9/20/2010)
I don't like the short version at all because it "lies".
That's very interesting, but, realistically, how often do people use nondeterministic functions like NEWID() in the comparison expression of a CASE statement?
Drew
I was thinking the same Drew, but what Jeff points out is that Simple and Searched CASE both operate by evaluating the expression for each condition until it "meets true". I've always distinguished between the two types and used Simple CASE where possible on the misunderstanding that it evaluates the condition only once and hence has to be faster. I can feel a test coming on...

Exactly. Do the same thing with a variable and it changes the Simple to a Searched CASE in the scalar operator. I haven't tested it but it sounds like that may take a little extra compile time. The only time you might notice is in recompile time over millions of rows, though.

Check it out...

DECLARE @SomeVariable INT

SELECT @SomeVariable = 1

SELECT TOP (1000)

CASE @SomeVariable --Produces 0 through 2

WHEN 0 THEN 'Zero'

WHEN 1 THEN 'One'

WHEN 2 THEN 'Two'

ELSE 'Unknown'

END

FROM Master.sys.All_Columns

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76999 More actions · Answer 3

Jeff Moden (9/30/2010)
Exactly. Do the same thing with a variable and it changes the Simple to a Searched CASE in the scalar operator. I haven't tested it but it sounds like that may take a little extra compile time. The only time you might notice is in recompile time over millions of rows, though.

Why would the number of rows affect (re)compile time? Isn't compilation independent of the number of rows unless you are running dynamic sql?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA