Dynamic SQL - Drop Table

Question

Post reply

Dynamic SQL - Drop Table

September 12, 2022 at 8:00 am

This was removed by the editor as SPAM

Viewing 11 posts - 46 through 55 (of 55 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

That is an interesting way to put it. Can I use that analogy sometime?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

November 20, 2006 at 9:42 am

#673008

Sure, you can quote me on this .

Sergiy SSC Guru Points: 110207 More actions · Answer 3

So, are you ready now to agree with my inital statement "Don't bother to drop temp table"?

Or you gonna insist on "best practices" to drop it?

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I already agreed with you 3 times. What do you need??????????

And yes I'll keep dropping 'em because my mammooth won't get scared by a cold fly .

Anyways for the one place I use a temp table in my app, there's really no point in debating this more .

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

I would vote to just drop it...

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Already been voted about 5 times... now we just need to go to supreme court with this and then we should be able to move on!

Jeff Moden SSC Guru Points: 1003919 More actions · Answer 7

Now, reverse the test... do it with the NO DROP first...

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

IIRC I had tested them in both orders. Do you have different results to show us?

Jeff Moden SSC Guru Points: 1003919 More actions · Answer 9

Yep... whichever one ran first on my box, lost by about a second. Then, I removed the drop from both, whichever one ran first, also lost by about a second. Single CPU box... didn't test on a "real" server.

I'm not implying that a drop should or should not be included... just thought it was interesting.

For the record, I don't do it either way I've only seen it once and it was with pooled connections, but I've seen it where a failure (timeout due to some other nasty code with a long explicit transaction) occurred part way through a proc scheduled to run once every 5 minutes... the temp table persisted on the connection and, of course, kept the job from running again. Instead of allowing the temp table to drop as Serqiy suggests (that's the way I used to do it on this one process) or doing an explicit drop at the end as Lynn suggests, I do a conditional drop at the very beginning just before the CREATE statement for the Temp table... a drop at the end didn't help when the proc failed due to a timeout because it never got executed. That also keeps me from mixing DDL and DML within the proc (all the DDL is done at the beginning) which supposedly cuts down on recompiles caused by mixing them (according to BOL).

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Now that would be an interesting test... Count the recompiles of each execution plans.

Anyone got time to set this one up?