Temp Tables in SQL Server

  • Heh... I don't know where they came up with the idea that SELECT INTO is slow... especially if the target DB has no triggers or keys and especially when the target is a Temp table that lives in TempDB with the SIMPLE recovery mode set...

    SELECT INTO enjoys the same mimimally logged and non-logged benefits in TempDB (again, SIMPLE recovery mode) as does Bulk Insert and BCP provided the following are true (from Books Online) ...

  • The recovery model is simple or bulk-logged.
  • The target table is not being replicated.
  • The target table does not have any triggers.
  • The target table has either 0 rows or no indexes.
  • The TABLOCK hint is specified. 
  • I will agree that a very large SELECT INTO will keep a lock on one of the system tables and will keep similar ops from running until it is complete. But, even for a million rows, if the query is quick, there won't be much interference (should be scant seconds).

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

Viewing post 46 (of 45 total)

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