| Author |
 Topic  |
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
 Posted - 06/02/2005 : 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
India
22461 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
India
22461 Posts |
Posted - 06/02/2005 : 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 |
Edited by - madhivanan on 06/02/2005 04:30:26 |
|
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
Posted - 06/02/2005 : 05:32:53
|
Kool :)
Thnx |
|
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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!
Australia
4970 Posts |
Posted - 06/02/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/02/2005 : 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. |
Edited by - Seventhnight on 06/02/2005 09:21:18 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/02/2005 : 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 - 06/02/2005 : 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
|
Edited by - X002548 on 06/02/2005 09:22:40 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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!
Australia
4970 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/03/2005 : 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
Sweden
3279 Posts |
Posted - 06/03/2005 : 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
United Kingdom
284 Posts |
Posted - 06/03/2005 : 05:18:13
|
| Cheers ears :) |
|
|
| |
Topic |
|
To drop or not to drop
Forum Locked
