Do you suffer from the pain of temporary tables hanging around long after you need them? Have you ever found yourself frantically googling, trying to remember the syntax to check for the existence of a temporary table before deleting it?
I, too, used to suffer alongside you, dear readers. I, too, know your pain of re-running a script only to find that a temporary table was skulking around in memory somewhere, waiting to ruin my carefully crafted three-value logic.
Well, my pain is gone thanks to my new found understanding of naming things in tempdb! You too can remove the terrible pain and suffering of searching for temp tables in your code and instead can use this one, that’s right only one, simple step to make sure that your temp tables are cleaned up by the time you need them again!
IF OBJECT_ID('tempdb..#magic') IS NOT NULL
DROP TABLE #magic;
You’re welcome. Please make all checks payable to ‘cash’.
Don’t forget about table variables for smaller applications — they clean up after themselves very nicely.
D. Lambert –
Very true. Table variables definitely have their place, especially when dealing with smaller amounts of data. (Nice post on table variables, by the way.)
However, the table variables will still be around (just like temp tables) if I’m messing around in SSMS before I run a CREATE PROCEDURE and commit my changes to the database. I can’t tell you the number of times I’ve forgotten to remove a temporary table. Then I look like an idiot in front the developers and they get one more reason to mock me apart from my TSQL license plate.
Plus, when you’re working with dynamic SQL, temp tables are reused by the code you execute via sp_executesql, whereas table variables are not (they’re out of scope), so you’ll have to make multiple calls to retrieve the same data. This way I can build dynamic SQl that uses the contents of temp tables for decision making as well as for joins in the final query.
Also, table variables are always assumed to have 1 row by the algebrizer. For my needs, the limitations of table variables render them largely useless since I deal largely with dynamic SQL working to aggregate large result sets (see Should I use a #temp table or a @table variable? for more). I’m including the link for others following along, not because I think you don’t know any better.