8

This title is the question. I am curious to know the answer. Someone told

select into is minimally logged in Simple recovery model database ... I didn't get into it at all.

Excerpt from Microsoft:

The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. 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

Seeking help

Thanks

migrated from stackoverflow.com Feb 13 '12 at 19:58

This question came from our site for professional and enthusiast programmers.

  • What database are you using? What structures are the tables? How did you measure that one is faster than the other? – Oded Jul 8 '11 at 9:39
  • I would be surprised if there was any difference on a well written DBMS. – James Anderson Jul 8 '11 at 9:40
  • Database: Sql server 20005... and I heard this.. even i am not 100% sure... I am looking for the what other peoples says.. As I mentioned that someone told me this.. – himadri Jul 8 '11 at 9:41
  • Found a link that confirms that SELECT INTO can be minimally logged when not using Full Recovery. – Damien_The_Unbeliever Jul 8 '11 at 9:45
9

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table. If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling. Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

  • Make sure your clustered index keys match when possible. This means there is no on-the-fly sorting

  • Disable all non-clustered indexes. Self-explanatory.

  • Set recovery mode to simple and trace flag 610 to ON. Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards. Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.

  • 3
    Very thorough answer JNK. Also, when implemented correctly and the recovery model is not full, a simple SSIS data flow task can be faster than either of these. Why? Both of the above will issue an exclusive lock (Read is multi-thread but write is single thread). As long as a table lock is used with the destination adapter, SSIS will use a bulk update lock (Both read and write are multi-thread). – brian Jul 9 '11 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy