To drop or not to drop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

To drop or not to drop

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 04:01:21

When dealing with our support people on a query I had with one of my procs they told me to remove the lines at the end of my proc which said to drop the temp tables I had created.

They said the drop table #mytable lines were not required as the tables were automatically destroyed once the proc ended.

Is this so? Or is it best practice to always explicitly drop temp tables?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 04:14:14

Yes he is true. No need to drop them explicity.It is important to note that only tables suffixed # will be dropped at the end of sp.Otherwise you have to explicitely drop that table.
You can use table variables in place of temp table if you want

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 04:21:39

Could you give me an example of a variable begin used instead of a temp table please?

Is there any benefit doing this?

Does the temp table actually exist on the database or is it in memory?

Thnx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 04:28:53

declare @t table(i integer, name varchar(50))
insert into @t values(1,'Name1')
insert into @t values(2,'Name2')
insert into @t values(3,'Name3')
Select * from @t

EDIT : No need to worry about dropping and will not take memory space

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 05:32:53

Kool :)

Thnx

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 09:07:54

So do you update a variable table the same as a created table?

I have modified my code from the created table version, first block of code, to the second block, the variable version but get a server error:

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '.'.

UPDATE #base
SET #base.produced = #temp.produced
FROM #base, #temp
WHERE #base.pak_no = #temp.pak_no

UPDATE @base
SET @base.produced = @temp.produced
FROM @base, @temp
WHERE @base.pak_no = @temp.pak_no

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 09:18:27

quote:

Or is it best practice to always explicitly drop temp tables?

I think so.

Damian
Ita erat quando hic adveni.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 09:20:36

Use an alias...

quote:
Originally posted by hog

So do you update a variable table the same as a created table?

I have modified my code from the created table version, first block of code, to the second block, the variable version but get a server error:

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '.'.

UPDATE #base
SET #base.produced = #temp.produced
FROM #base, #temp
WHERE #base.pak_no = #temp.pak_no

UPDATE base
SET base.produced = temp.produced
FROM @base base, @temp temp
WHERE base.pak_no = temp.pak_no

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 09:20:49

UPDATE B
SET B.produced = T.produced
FROM @base B, @temp T
WHERE B.pak_no = T.pak_no

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 09:21:13

WOW: A double snipe

It's 9:00 Thursday night in Australia..don't you have a gig or something?

Gotta chime in here...It is ALWAYS a good coding practice to explicity DROP a temp table before you exit the sproc.

What reason did they give to remove it.

My guess is that they don't know what they're talking about, and are just grasping at straws.

These are the most dangerous types of "tech support"

Please post your 2 temp or table variable definitions. See the link below to get your problem resolved quickly.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 09:23:23

quote:

My guess is that they don't know what they're talking about, and are just grasping at straws.

These are the most dangerous types of "tech support"

I'm in total agreeance. I had to yell at a client's DBA the other day over a similar issue, I like winning

Edit : It's actually 11pm. I'm trying to clean up a couple of things for another client, but procrastinating here instead

Damian
Ita erat quando hic adveni.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 09:34:29

So basicaly as a good rule I should drop tables I create whether variable or # type?

The support twits just told me to remove the lines as they get dropped when the proc closes?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 09:36:04

Also the alias works, but I dont understand why this should be necessary. can you explain please?

Thnx

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 09:38:41

You can't drop a table variable, but I drop temp tables.

In theory, the temp tables will get dropped when your connection closes. However I think it's good practice to dispose of any resources I've allocated. If I'm reviewing code and a temp table wasn't dropped, I'd send it back.

I say pick a fight with the support idiots and ask them to justify not doing it.... but that's just me

Damian
Ita erat quando hic adveni.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 09:43:14

Well I always made it a habit of closing connections, setting recordsets etc to nothing when functions closed in my VB progs, but am new to T-SQL so took their.........advice?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-03 : 03:47:27

Can someone explain why the alias suggestion works and the other method does not please?

UPDATE #base
SET #base.produced = #temp.produced
FROM #base, #temp
WHERE #base.pak_no = #temp.pak_no

UPDATE A
SET A.produced = B.produced
FROM @base A, @temp B
WHERE A.pak_no = B.pak_no

I can't see what the difference is?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-03 : 05:02:05

I have noticed that there are some syntax restrictions whern working with table variables.
specifically, you need to alias them.
I have no explanation though.

Drop temporary tables when You are done with them.
I believe that is better programming practice than leaving all the dirty work to SQL Server.

rockmoose

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-03 : 05:18:13

Cheers ears :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources