none
Local temp table created inside stored procedure does not drop automatically at the end of current session

    Question

  • Here I just want to play with local temp table behavior inside a stored procedure. I still create and drop temp table inside the same proc.

    Below is the sample proc:

    /*********************************************/

    CREATE PROCEDURE dbo.test2

    AS
    /*
    exec dbo.test2
    */
    begin
    begin try
        CREATE TABLE #test2(test2 int)

    return
    end try
    begin catch
        select 'This is inside CATCH statement of proc test2!'
    return
    end catch
    end
    GO

    /*********************************************/

    After I close the query window that runs "exec dbo.test2" and the conn session is supposed to be closed(?). When I open another query window and run the query below:

    select * from tempdb.sys.columns 
    where name in ('test2')

    I still see this record there, which means the temp table still exists after the session is closed(?). This column is unique for sure.

    Any explanation here?

    Thanks.

    Shigui,


    • Edited by ysgui Monday, July 29, 2013 11:18 PM
    Monday, July 29, 2013 11:08 PM

Answers

  • Well, a partial explanation.

    Since SQL 2005 when a #temp table is dropped, it isn't *really* dropped but only renamed.  This is apparently in the expectation that it is only going to get recreated anyway, so it is more efficient to save the old definition and rename it back again than to munge the system tables deleting and creating it.

    Now, you can't see the tempdb.sys.column named 'test2' from the same session that created it, only from another session!  This reveals some of the voodoo that tempdb is doing with it's rename shuffle.  EXACTLY how that works I don't know, maybe you can Bing it out, but apparently it involves the sessionid.

    But you will notice, you can rerun your SP without getting an exception, so the table *has* been dropped, more or less.  :)

    Josh

    • Marked as answer by ysgui Wednesday, July 31, 2013 12:22 AM
    Tuesday, July 30, 2013 2:14 AM
  • Have a look at the Temp Caching well explained by Paul White http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Tom PhillipsModerator Tuesday, July 30, 2013 8:48 PM
    • Marked as answer by ysgui Wednesday, July 31, 2013 5:05 PM
    Tuesday, July 30, 2013 2:21 AM
  • Thanks JRStern.

    I notice that columns create_date and modify_date have different timestamp after I run the same proc again. I think the server re-use the temp table definition to avoid the DMV locking from concurrency

    • Marked as answer by ysgui Wednesday, July 31, 2013 12:26 AM
    Wednesday, July 31, 2013 12:25 AM

All replies

  • Well, a partial explanation.

    Since SQL 2005 when a #temp table is dropped, it isn't *really* dropped but only renamed.  This is apparently in the expectation that it is only going to get recreated anyway, so it is more efficient to save the old definition and rename it back again than to munge the system tables deleting and creating it.

    Now, you can't see the tempdb.sys.column named 'test2' from the same session that created it, only from another session!  This reveals some of the voodoo that tempdb is doing with it's rename shuffle.  EXACTLY how that works I don't know, maybe you can Bing it out, but apparently it involves the sessionid.

    But you will notice, you can rerun your SP without getting an exception, so the table *has* been dropped, more or less.  :)

    Josh

    • Marked as answer by ysgui Wednesday, July 31, 2013 12:22 AM
    Tuesday, July 30, 2013 2:14 AM
  • Have a look at the Temp Caching well explained by Paul White http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Tom PhillipsModerator Tuesday, July 30, 2013 8:48 PM
    • Marked as answer by ysgui Wednesday, July 31, 2013 5:05 PM
    Tuesday, July 30, 2013 2:21 AM
  •  After I close the query window that runs "exec dbo.test2" and the conn session is supposed to be closed(?). When I open another query window and run the query below:

    Hello,
    Closing the query windows will not closed the current connection session. You can try to restart the instance of the current connection under the Object Explorer of SSMS or create a new connection.

    If you have any question, please feel free to ask.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Tuesday, July 30, 2013 2:54 AM
    Moderator
  • Thanks JRStern.

    I notice that columns create_date and modify_date have different timestamp after I run the same proc again. I think the server re-use the temp table definition to avoid the DMV locking from concurrency

    • Marked as answer by ysgui Wednesday, July 31, 2013 12:26 AM
    Wednesday, July 31, 2013 12:25 AM
  • Pretty much session _id is the column spid from sp_who2. When I close one query window, I do not see that spid anymore. Does that mean the session is closed?
    Wednesday, July 31, 2013 12:51 AM

  • Closing the query windows will not closed the current connection session.

    Yes it will.  In SSMS each Query Window has its own connection and closing the query window closes that connection. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 31, 2013 12:56 AM