Home Forums SQL Server 2005 SQL Server 2005 General Discussion Is it "better" to specify the definition of a temporary table, or just define it on the fly? RE: Is it "better" to specify the definition of a temporary table, or just define it on the fly?

  • November 6, 2012 at 2:33 pm

    #1556583

    GSquared (11/6/2012)

    Lee Crain (11/6/2012)

    Jeff Moden,

    Please clarify this subject for me:

    I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

    Is this true or is it incorrect information?

    Thanks,

    Used to be partially true in SQL 7.5 and before.

    I believe you meant 6.5 and before. SP1 of 6.5 fixed the problem and it's been a built in fix since 7.0. It still puts on some shared locks in TempDB which will make the SSMS GUI give a time out when you right click on tempdb for properties but it's so fast that it doesn't usually matter.

    As of 2005, it still has a problem if the source is from a linked server (personal experience... no written proof). We did this once and it caused massive timeouts on the source server. You can try it but be aware of the possible consequences.

    I do have a Microsoft link for the 6.5/SP1 thing at home. I'll try to remember to post it when I get hoem.

    --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)