DaveWentzel.com All Things Data
Do You Explicitly Drop Your Temp Tables?
Do you explicitly drop your #TempTables after you are through with them in your stored procedures? Whenever I'm asked to performance tune a stored procedure and I see an explicit DROP #TempTable at the bottom of the code, I remove it. Another developer asked me today why I do this. I just assumed this would be common sense, but I guess not.
4GL coders were taught to close object references at the end of their code block. This isn't taught as much these days since most languages have "garbage collection" that handles this for you, generally. This same rule holds true for stored procedures. I could provide you with some test scripts using the standard FOO and BAR tables, but just thinking through the logic should be enough to convince you that a specific DROP is not needed.
Performing a DROP, no matter how small the temp table, still takes some amount of time. While the DROP is executing the procedure still may be holding locks (depending on your isolation levels, etc) that it may not need to. Instead, let the proc complete and allow SQL Server to naturally clean up the temporary objects for you. Remember that #TempTables are scoped to the code block that created the object, as well as any children, and when the procedure execution completes they are destroyed naturally since they are then out of scope.
One More Reason
Here's one more reason...many data access utilities and ORMs require the temp tables to NOT be DROP'd at the end of the procedure. Most people don't know this. A very popular ORM (which will remain nameless) cannot generate a "model" against a stored procedure that SELECTs data out of a #TempTable. The ORM "model" is basically a way to map the underlying OO model to your stored procedure inputs and outputs. The "model generator" reads metadata about the stored procedure to determine what its output result set looks like (column names, order, and data types). Suffice it to say that natively SQL Server does not provide the necessary metadata to satisfy the ORM model generator, so it barfs. The vendor even states that #TempTables are not supported, use @TableVariables instead. Nevermind that this could have drastic performance ramifications. Please don't think this is just bad ORM vendor code...SSRS has similar problems and the advertised workaround is to SET FMTONLY OFF within the procedure. This trick also works with the ORM model generator, but only if you do NOT explicitly DROP your #TempTables.
Again, granted, the performance improvement likely won't be much, but it is still a Best Practice to not explicitly DROP your #TempTables. In my opinion anyway.