title: Drop or not drop temporary tables in stored procedures
body: <p>I saw this question quite a many times but I couldn't get the answer that would satisfy me. Basically what people and books say is "<em>Although temporary tables are deleted when they go out of scope, you should explicitly delete them when they are no longer needed to reduce resource requirements on the server</em>".</p>
  
  <p>It is quite clear to me that when you are working in management studio and creating tables, then until you close your window or disconnect, you will use some resources for that table and it is logically that it is better to drop them.</p>
  
  <p>But when you work with procedure then if you would like to cleanup tables most probably you will do that at the really end of it (I am not talking about the situation when you drop the table as soon as you really do not need that in the procedure). So the workflow is something like that :</p>
  
  <p>When you drop in SP:</p>
  
  <ul>
  <li>Start of SP execution</li>
  <li>Doing some stuff</li>
  <li>Drop tables</li>
  <li>End of execution</li>
  </ul>
  
  <p>And as far as I understand how can it possibly work when you do not drop:</p>
  
  <ul>
  <li>Start of SP execution</li>
  <li>Doing some stuff</li>
  <li>End of execution</li>
  <li>Drop tables</li>
  </ul>
  
  <p>What's the difference here? I can only imagine that some resources are needed to identify the temporary tables. Any other thoughts?</p>
  
  <p>UPDATE:</p>
  
  <p>I ran simple test with 2 SP:</p>
  
  <pre><code>create procedure test  as
  begin
  create table #temp (a int)
  insert into #temp values (1);
  drop table #temp;
  end
  </code></pre>
  
  <p>and another one without drop statements. I've enabled user statistics and ran the tests:</p>
  
  <pre><code>declare @i int = 0;
   while @i &lt; 10000
   begin
   exec test;
   SET @i= @i + 1;
   end
  </code></pre>
  
  <p>That's what I've got (Trial 1-3 dropping table in SP, 4-6 do not dropping)
  <a href="https://i.stack.imgur.com/BCISe.png" rel="noreferrer"><img src="https://i.stack.imgur.com/BCISe.png" alt="enter image description here"></a></p>
  
  <p>As the picture shows that all stats are the same or decreased a bit when I do not drop temporary table.</p>
  
  <p>UPDATE2:</p>
  
  <p>I ran this test 2nd time but now with 100k calls and also added SET NOCOUNT ON. These are the results:
  <a href="https://i.stack.imgur.com/XTFDe.png" rel="noreferrer"><img src="https://i.stack.imgur.com/XTFDe.png" alt="enter image description here"></a></p>
  
  <p>As the 2nd run confirmed that if you do not drop the table in SP then you actually save some user time as this is done by some other internal process but outside of the user time.</p>