title: What are the dangers of BEGIN TRY DROP TABLE?
body: <p>In a script used for interactive analysis of subsets of data, it is often useful to store the results of queries into temporary tables for further analysis.</p>
  
  <p>Many of my analysis scripts contain this structure:</p>
  
  <pre><code>CREATE TABLE #Results (
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL
  );
  
  INSERT INTO #Results (a, b, c)
  SELECT a, b, c
  FROM ...
  
  SELECT *
  FROM #Results;
  </code></pre>
  
  <p>In SQL Server, temporary tables are connection-scoped, so the query results persist after the initial query execution. When the subset of data I want to analyze is expensive to calculate, I use this method instead of using a table variable because the subset persists across different batches of queries.</p>
  
  <p>The setup part of the script is run once, and following queries (<code>SELECT * FROM #Results</code> is a placeholder here) are run as often as necessary.</p>
  
  <p>Occasionally, I want to refresh the subset of data in the temporary table, so I run the entire script again. One way to do this would be to create a new connection by copying the script to a new query window in Management Studio, I find this difficult to manage.</p>
  
  <p>Instead, my usual workaround is to precede the create statement with a conditional drop statement like this:</p>
  
  <pre><code>IF OBJECT_ID(N'tempdb.dbo.#Results', 'U') IS NOT NULL
  BEGIN
    DROP TABLE #Results;
  END;
  </code></pre>
  
  <p>This statement correctly handles two situations:</p>
  
  <ol>
  <li>On the first run when the table does not exist: do nothing.</li>
  <li>On subsequent runs when the table does exist: drop the table.</li>
  </ol>
  
  <p>Production scripts written by me would always use this method because it raises no errors for in the two expected situations.</p>
  
  <p>Some equivalent scripts written by my fellow developers sometimes handle these two situations using exception handling:</p>
  
  <pre><code>BEGIN TRY DROP TABLE #Results END TRY BEGIN CATCH END CATCH
  </code></pre>
  
  <p>I believe in the database world it is better always to ask permission than seek forgiveness, so this method makes me uneasy.</p>
  
  <p>The second method swallows an error while taking no action to handle non-exceptional behavior (table does not exist). Also, it is possible that an error would be raised for a reason other than that the table does not exist.</p>
  
  <p>The <a href="http://www.wiseowl.co.uk/blog/s183/drop-temporary-tables.htm" rel="nofollow">Wise Owl</a> warns about the same thing:</p>
  
  <blockquote>
    <p>Of the two methods, the [<code>OBJECT_ID</code> method] is more difficult to understand but
    probably better: with the [<code>BEGIN TRY</code> method], you run the risk of trapping
    the wrong error!</p>
  </blockquote>
  
  <p>But it does not explain what the practical risks are.</p>
  
  <p>In practice, the <code>BEGIN TRY</code> method has never caused problems in systems I maintain, so I'm happy for it to stay there.</p>
  
  <p>What possible dangers are there in managing temporary table existence using <code>BEGIN TRY</code> method? What unexpected errors are likely to be concealed by the empty catch block?</p>