Temporary table in a batch

Question

Post reply

Temporary table in a batch

Anoo S Pillai

Mr or Mrs. 500

Points: 547
More actions
February 8, 2014 at 1:01 pm

#283531

Comments posted to this topic are about the item Temporary table in a batch
February 10, 2014 at 1:45 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 25 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Interesting question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Interesting "feature" of SQL Server 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Nice question.

Not sure why someone would try to create the same temp table twice in the same procedure. If needing to use the same temp table then clear it and reinsert. Or just use a new temp table with a new name.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Nice question. It's by design.

Thanks.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 5

Nice one thanks.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Nice question.

But the explanation appears to have swallowed the good old party line: sometimes (and it seems reasonably certain that this is one of those times) "it's by design" means "someone made a mess of this, but there's not much point in fixing it so we'll pretend that's what was intended". I've watched that happen in standard writing as well as in code writing and in requirements writing; sometimes the result is pretty harmless, as here, but at other times it is far from harmelss - but the culprits still spout the "it's by design" mantra.

As for

explanation
Books online states it vaguely as
“If more than one temporary table is created inside a single stored procedure or batch, they must have different names.”
Detailed explnation is available at - http://www.tectalks.com/493/temporary-table-could-not-be-re-created/

there is nothing at all vague about the BOL statement, and the "Detailed explanation" doesn't say anything that BOL doesn't.

Tom

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 7

Also the Begin is missing after AS at the start 🙂

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

L' Eomot Inversé (2/9/2014)
Nice question.
But the explanation appears to have swallowed the good old party line: sometimes (and it seems reasonably certain that this is one of those times) "it's by design" means "someone made a mess of this, but there's not much point in fixing it so we'll pretend that's what was intended". I've watched that happen in standard writing as well as in code writing and in requirements writing; sometimes the result is pretty harmless, as here, but at other times it is far from harmelss - but the culprits still spout the "it's by design" mantra.
As for
explanation
Books online states it vaguely as
“If more than one temporary table is created inside a single stored procedure or batch, they must have different names.”
Detailed explnation is available at - http://www.tectalks.com/493/temporary-table-could-not-be-re-created/
there is nothing at all vague about the BOL statement, and the "Detailed explanation" doesn't say anything that BOL doesn't.

+ 1, Agree.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

bitbucket-25253 (2/8/2014)
Interesting question

+ 1, yes.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

free_mascot One Orange Chip Points: 27169 More actions · Answer 10

Good One.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Good question. I think this is all about SQL being a declarative language.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Umer Akram SSC-Insane Points: 22209 More actions · Answer 12

Umer Akram

SSC-Insane

Points: 22209

February 10, 2014 at 6:34 am

#1687508

nice question ... thanks for sharing

Hany Helmy SSChampion Points: 13921 More actions · Answer 13

SQLRNNR (2/9/2014)
Nice question.
Not sure why someone would try to create the same temp table twice in the same procedure. If needing to use the same temp table then clear it and reinsert. Or just use a new temp table with a new name.

It`s just not practical to create the same temp table twice in the same SP, there is no logic to do that, but the question was interesting enough that I had to test it my self as never did that before.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant