Richard Swinbank walks through a technique to drop temp tables as a procedure begins:
Local temporary tables – tables with names that begin with a single
#character – are dropped automatically by SQL Server when they are no longer in scope. So why drop them explicitly at all?I’m writing this from the perspective of a data engineer. ETL processes or their components are often implemented as T-SQL stored procedures (SPs) which might move significant amounts of data around and take a while to run. I’m not talking about the sort of SP that needs to run in 30ms to guarantee application responsiveness!
Sometimes it’s useful to store intermediate results in temporary tables, sometimes it’s necessary, and sometimes you find yourself maintaining code built by someone who just really loves them. Adding explicit
DROPs to code like this can make development and maintenance a bit smoother.
My concern with doing this is that it prevents temporary object caching. Granted, Richard explicitly calls out that these are long-running procedures (and probably one of a particular procedure running at a time), so that does reduce the concern accordingly.