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
INSERT INTO?



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.

No comments:

Post a Comment