Temp Tables in SQL Server

Question

Temp Tables in SQL Server

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)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

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