none
Is it necessary to clean up (drop) temporary tables in stored procedures?

    Question

  • I often see cleanup code like this inside stored procedures.  But my understanding is that temporary tables will always fall out of scope when the procedure completes execution. Is there any reason to drop temporary tables like this?  Other than "It just feels right!"

    CREATE PROCEDURE dbo.MyProc AS BEGIN CREATE TABLE #TempTest (Val INT) --do some work here

    --clean up DROP TABLE #TempTest END GO


    • Changed type Naomi NModerator Thursday, July 11, 2013 3:28 PM Question rather than discussion
    Thursday, July 11, 2013 1:53 AM

Answers

  • Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can't see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure:

        A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.
        Other local temporary tables are dropped when the session ends.

    Global temporary tables (start with ##) are shared between sessions. They are dropped when:

        The session that created them ends
        AND no other session is referring to them

    This command can be handy to see which temporary tables exist:

    select TABLE_NAME from tempdb.information_schema.tables

    And this is handy to drop temporary tables if you're not sure they exist:

    if object_id('tempdb..#SoTest') is not null drop table #SoTest

    Many Thanks & Best Regards, Hua Min

    Thursday, July 11, 2013 4:02 AM
  • It is good practice to clean it up, if you are not going to use it.

    yes it will get cleaned up automatically, but only when user ends its session.

    consider you have 100 users going to run this SP,so untill the disconnect this 100 #temp will be there,

    so why not you only clean it up if you no more required. Right...


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, July 11, 2013 2:01 AM

All replies

  • It is good practice to clean it up, if you are not going to use it.

    yes it will get cleaned up automatically, but only when user ends its session.

    consider you have 100 users going to run this SP,so untill the disconnect this 100 #temp will be there,

    so why not you only clean it up if you no more required. Right...


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, July 11, 2013 2:01 AM
  • Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can't see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure:

        A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.
        Other local temporary tables are dropped when the session ends.

    Global temporary tables (start with ##) are shared between sessions. They are dropped when:

        The session that created them ends
        AND no other session is referring to them

    This command can be handy to see which temporary tables exist:

    select TABLE_NAME from tempdb.information_schema.tables

    And this is handy to drop temporary tables if you're not sure they exist:

    if object_id('tempdb..#SoTest') is not null drop table #SoTest

    Many Thanks & Best Regards, Hua Min

    Thursday, July 11, 2013 4:02 AM
  • Since SQL Server 2005 there is no need to drop a temporary tables, even more if you do it may requires addition IO. The MS introduce temp caching that should reduce the costs associated with temp table creation. The second temp table creation is much faster. Instead 
    of dropping and creating the table it simply truncates it. All indexes and 
    statistics are truncated and identity column values are reset. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 6:50 AM
    Answerer
  • More clear explanation is here in the below article:

    http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    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.

    Thursday, July 11, 2013 7:16 AM
  • >> I often see cleanup code like this inside stored procedures. But my understanding is that temporary tables will always fall out of scope when the procedure completes execution. Is there any reason to drop temporary tables like this? Other than "It just feels right!"
    <<

    It feels right :) It just like we used to do it with tapes. Mount the scratch tape on a drive; do the job; dismount the scratch tape on a drive.

    But the real question is why would a competent SQL programmer mimic a 1950's scratch tape architecture in T-SQL? Properly written declarative code does not use any local variables of any kind. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by quilts123 Tuesday, December 17, 2013 10:40 AM
    Thursday, July 11, 2013 2:52 PM
  • it depends on the session you are working on. if the session gets over after the execution, there is no need to drop temp table. else doing it considered in good practise to follow.
    Thursday, July 11, 2013 3:50 PM
  • Thanks for the links, Latheesh.  I couldn't find any compelling reason to consider DROPing temporary tables to be a best practice, in the articles or in this discussion.

    In the second article Paul White implies a DROP is not necessary, when discussion temp table caching:

    Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS.  None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).

    Friday, July 12, 2013 6:22 PM