Post reply

When to drop temp table?

  • August 22, 2012 at 10:22 pm

    #280646

    Hi friends,

    Some time back we have discussed about dropping temp table, but here I have another question: the scenario is, I have one log running SP, and inside this I'm checking whether the #table is existing and if not I'm creating it ( say I'm creating #t1 , #t2 and #t3 ) . In this #t1 and #t2 is getting data from some physical tables and they are used to insert data into #t3 (by joining with some physical tables).

    Now it is a best practice to drop the #tables and for this I have two options:

    1. Drop all the #tables at the end of the SP

    2. Drop #t1 and #t2 just after its usage (after loading the data into #t3) and drop #t3 at the end.

    In tempDB space point of view which among the above two options is best?

    Thanks & Regards,
    MC

  • August 22, 2012 at 10:27 pm

    #1529025

    Typically, you should release resources as soon as you are finished with them.

  • August 23, 2012 at 8:55 am

    #1529304

    Thanks..will try this.

    Thanks & Regards,
    MC

  • August 23, 2012 at 9:02 am

    #1529314

    I'm trying to find the thread, but we once did a specific test here on the forums about whether there is a performance hit on whether you explicitly drop a temp table when you are done with it, vs letting SQL handle it when it goes out of scope;

    the results, as I remember it, was to let SQL handle it automatically; there was a performance hit in the execution plan as it dropped the tables explicitly.

    Trying my Google-Fu now, to see if I can find that thread.....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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