Drop #Temp Table
-
Ok, I had this code before and now I can't find it, how do I get the code do drop a temp table at the start of my query.
Right now I am saying
Drop table #Temp
Select top 10 * from tableX into #TEMP
The first time that I run this I get an error statement, I need to put something into an SP and I have a lot of temp tables, I can't remember the code.
-
if object_id( 'tempdb..#Temp' ) is not null drop table #Temp
-
If you Delcare a @TableVariable, you do not need to drop it to re-run the script...
I wasn't born stupid - I had to study.
-
Declaring a table allow you to do normal stuff on it and have a type specific column - you then don't end up with foat when sql can't do correct formatting. Also the table is limited to the current scope of the query - no need drop as said by Farrel keough. Also I read a post somewhere
that variable tables in much faster and beter that temp tables - think it was article "Temp Tables in SQL Server"
-
Table variables are not necessarily faster than Temp tables... they do not and cannot be made to use statistics...
Also, if you think that table variables might be faster because they live in memory, you really need to read the following URL... table variables and temp tables both start out in memory and they both spill into TempDB when they start to get large.
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
--Jeff Moden
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
They also have scope issues. But, for quick a dirty, they are great! And, if you do not have large sets of data, they work dandy as well as Indexing is generally not a concern.
It is just less typing in Query Analyzer...
I wasn't born stupid - I had to study.
-
It is just less typing in Query Analyzer...
... unless you cheat and use SELECT/INTO... if you are confident that the code won't crash in the middle, you don't even need a "Drop" on either end of the proc (although I always include both just 'cause I'm a bit regimented [polite word for "anal"
) because Temp Tables are fairly scope sensitive as well. Can't even do a SELECT/INTO with a table variable.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
...and as we all know it is a BIG no-no doing SELECT ... INTO ... FROM ... since it holds locks on a lot of system tables for the duration of the statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply