June 23, 2006 at 4:26 am
I have inherited a stored proc which declares a temporary table thus:
DECLARE @WeekendDates Table (WeekendDate datetime, FridaysDate datetime)
This table is then used in joins further on in the sp, and data from it is used in sub queries too. When running the sp, which is a slow one (half hour on a good day), I see 164 EXT locks in the tempdb node of Locks / Object in Enterprise Manager. The server CPU goes to 100%.
While there are other things I need to change in this sp to improve performance (eg the sub queries), I noticed that there is no explicit DROP TABLE @WeekendDates. So I added the drop at the end of the sp, but the syntax checker rejected it.
BOL is pretty basic about drop table, as you'd expect (not much to say, is there?). It did tell me that temp tables would exist until the connection that spawned them was dropped, but I would like to be explicit about this. Is it possible?
Thanks in advance,
Bill.
June 23, 2006 at 5:00 am
No, you can't do this. There is nothing wrong with the described use of a table variable, so don't worry about it.
Table variables are just like any other local variable, and are cleaned up automatically when they go out of scope. You can't clean them up yourself explicitly.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
June 23, 2006 at 9:54 am
Thanks very much!
Bill.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply