Saturday, September 4, 2010
SELECT...INTO is faster than CREATE & INSERT
Do you use SELECT..INTO
in your code / SPs?
Or you prefer to create
a new table and then use
Anyway, you should be aware of the following:
The SELECT ... INTO is minimally logged operation in case
your database under the simple recovery or bulk-logged
recovery model. See more info here.
Therefore,with minimal logging, using the SELECT… INTO
statement can be more efficient than creating a table and then
populating the table with an INSERT statement.
In most of the cases we use Temp tables when we use
SELECT ... INTO.
The temp table created in the TEMPDB, right?
And as you know the recovery mode of the TEMPDB is simple.
Therefore the SQL Server will use minimal logging for our new table.
I highly recommend to you to read the Chintak`s post about this
subject. He provides real examples.