My view is, first see if you really need a temp table - or - can you make do with a Common Table Expression (CTE). Second, I would always drop my temp tables. Sometimes you need to have a temp table scoped to the connection (e.g. ##temp), so if you run the query a second time, and you have explicit code to create the temp table, you'll get an error that says the table already exists. Cleaning up after yourself is ALWAYS a good software practice.
Another alternative is a TABLE variable, which will fall out of scope once the query completes:
DECLARE @MyTable AS TABLE ( MyID INT, MyText NVARCHAR(256) ) INSERT INTO @MyTable VALUES (1, 'One'), (2, 'Two'), (3, 'Three') SELECT * FROM @MyTable
问题1:
Table variables are automatically local and automatically dropped -- you don't have to worry about it.
+1 - Also you can't drop them even if you wanted to - they persist as long as the session is open, just like any other variable. They are also unaffected by transactions. – JNK Apr 13 '11 at 18:04
问题2:
if somebody else comes across this... and you really need to drop it like while in a loop, you can just delete all from the table variable:
DELETE FROM @tableVariableName