<p>In a stored procedure, when is #Temptable created in SQL Server 2005?  When creating the query execution plan or when executing the stored procedure?</p>

<pre><code>if (@x = 1)
    begin
        select 1 as Text into #Temptable
    end
else
    begin
        select 2 as Text into #Temptable
    end
</code></pre>

[{'answer_id': 43910, 'author': 'Chris Miller', 'author_id': 206, 'author_profile': 'https://Stackoverflow.com/users/206', 'pm_score': 2, 'selected': False, 'text': "<p>It's created when it's executed and dropped when the session ends.</p>\n"}, {'answer_id': 43925, 'author': 'Scott Lawrence', 'author_id': 3475, 'author_profile': 'https://Stackoverflow.com/users/3475', 'pm_score': 2, 'selected': True, 'text': '<p>Interesting question.</p>\n\n<p>For the type of temporary table you\'re creating, I think it\'s when the stored procedure is executed.  Tables created with the # prefix are accessible to the SQL Server session they\'re created in.  Once the session ends, they\'re dropped.</p>\n\n<p>This url: <a href="http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx" rel="nofollow noreferrer">http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx</a> seems to indicate that temp tables aren\'t created when query execution plans are created.</p>\n'}, {'answer_id': 44895, 'author': 'Scott Bennett-McLeish', 'author_id': 1915, 'author_profile': 'https://Stackoverflow.com/users/1915', 'pm_score': 1, 'selected': False, 'text': "<p>Whilst it may be automatically dropped at the end of a session, it is good practice to drop the table yourself when you're done with it.</p>\n"}, {'answer_id': 77262, 'author': 'Chris Wuestefeld', 'author_id': 10082, 'author_profile': 'https://Stackoverflow.com/users/10082', 'pm_score': 2, 'selected': False, 'text': "<p>You might also want to consider table variables, whose lifecycle is completely managed for you.</p>\n\n<pre><code>DECLARE @MyTable TABLE (MyPK INT IDENTITY, MyName VARCHAR(100))\nINSERT INTO @MyTable ( MyName ) VALUES ( 'Icarus' )\nINSERT INTO @MyTable ( MyName ) VALUES ( 'Daedalus' )\nSELECT * FROM @MyTable\n</code></pre>\n\n<p>I almost always use this approach, but it does have disadvantages. Most notably, you can only use indexes that you can declare within the TABLE() construct, essentially meaning that you're limited to the primary key only -- no using ALTER TABLE.</p>\n"}]
['https://Stackoverflow.com/questions/43903', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2184/']