Temp table

Question

Temp table

sushil_dwid

Ten Centuries

Points: 1241
More actions
November 23, 2015 at 12:57 am

#302447

Do you think we should drop temp table explicitly?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

I always drop it explicitly at the top of the script using an if exists, when its no longer needed or at the end of the script.

That way if the same session gets called to run the same proc again you know the session is clean for the temp tables in question and thus you wont get object already exists errors

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I wouldn't bother. If a temp table is created in a procedure, it's automatically dropped when the procedure ends, so there's no chance of getting errors if the procedure runs a second time on the same session.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 3

Yes we can write but you do you think it would be helpful.Paul's blog suggest that it would not be required.

http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

GilaMonster (11/23/2015)
I wouldn't bother. If a temp table is created in a procedure, it's automatically dropped when the procedure ends, so there's no chance of getting errors if the procedure runs a second time on the same session.

Well I never knew that, thanks for that tidbit Gail.

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 5

agreed with Gail but does it not involves in creating deadlock or blocking other objects?

we call this proc at front end from one of the report and this report used by different users or same user in different session or same session?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Does what not involve deadlocks?

we call this proc at front end from one of the report and this report used by different users or same user in different session or same session?

What are you asking here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100948 More actions · Answer 7

I don't think a global temp table would necessarily be immediately dropped. But overall I agree, there's no great concern to drop temp tables in the code, unless perhaps:

1) you have a very large number of temp tables; and/or

2) the temp tables are (very) large; and/or

3) you have a very long amount of processing to do later in the same proc.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.