Post reply

Do i need to drop temp tables ?

  • November 2, 2012 at 11:51 pm

    #270447

    Hi,

    I have a SP in which i create 3 temp tables. and get result from those tables. But at the end of sp do i need to drop those temp tables ? or those will be deleted from memory automatically ?

    in versions of 2000,2005,2008

    thanks

  • November 2, 2012 at 11:56 pm

    #1555725

    its always good idea to write drop statement in your sp.

    However temp tables gets automatically dropped when you close your Query window .

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • November 3, 2012 at 12:12 am

    #1555728

    thanks yaar

  • November 3, 2012 at 2:09 am

    #1555732

    Temp tables are automatically dropped as soon as they go out of scope (the proc that they were created in completes) or the connection that created them closes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 3, 2012 at 10:07 am

    #1555768

    KcV (11/2/2012)

    Hi,

    I have a SP in which i create 3 temp tables. and get result from those tables. But at the end of sp do i need to drop those temp tables ? or those will be deleted from memory automatically ?

    in versions of 2000,2005,2008

    thanks

    No... you don't need to drop temp tables.

    That notwithstanding, I tend to do a conditional drop at the beginning of a sproc and it has nothing to do with any effect on the spoc. Rather, they are an artifact from development and testing prior to conversion to a stored procedure. I leave the conditional drop in place for the next person who may have to troubleshoot the code. It costs almost nothing to do the conditional drop and it also anyone reading the code know that there are temp tables somewhere in the proc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 3, 2012 at 10:12 am

    #1555769

    Jeff Moden (11/3/2012)

    KcV (11/2/2012)

    Hi,

    I have a SP in which i create 3 temp tables. and get result from those tables. But at the end of sp do i need to drop those temp tables ? or those will be deleted from memory automatically ?

    in versions of 2000,2005,2008

    thanks

    No... you don't need to drop temp tables.

    That notwithstanding, I tend to do a conditional drop at the beginning of a sproc and it has nothing to do with any effect on the spoc. Rather, they are an artifact from development and testing prior to conversion to a stored procedure. I leave the conditional drop in place for the next person who may have to troubleshoot the code. It costs almost nothing to do the conditional drop and it also anyone reading the code know that there are temp tables somewhere in the proc.

    I do the same thing.

    IF OBJECT_ID(N'tempdb..#MyTempTable') IS NOT NULL

    DROP TABLE #MyTempTable;

    CREATE TABLE #MyTempTable (column list);

    No need to drop them at the end, but I find the above useful for refactoring/debugging/documenting/etc.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply