CODEWITHSUNDEEP
sqlsql-servert-sql
veljasije
veljasije

Reputation: 7092

Logging of different species of INSERT statement

There is a four kind of inserting data in table in SQL:

INSERT INTO TableName (ColumnList) VALUES (ValuesList)

INSERT INTO TableName (ColumnList) SELECT Columns FROM OtherTable

INSERT INTO TableName (ColumnList) EXEC SomeProc

SELECT Columns INTO TableName FROM OtherTable

Every INSERT statement is logged in transaction log, and my question is what kind of INSERT has minimal logging?

What is the order in using, based on performance?

Upvotes: 2

Views: 1237

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

The Data Loading Performance Guide has a good summary of minimally logged operations:

To support high-volume data loading scenarios, SQL Server implements minimally logged operations. Unlike fully logged operations, which use the transaction log to keep track of every row change, minimally logged operations keep track of extent allocations and metadata changes only. Because much less information is tracked in the transaction log, a minimally logged operation is often faster than a fully logged operation if logging is the bottleneck. Furthermore, because fewer writes go the transaction log, a much smaller log file with a lighter I/O requirement becomes viable.

Out of the different types of insert statements you provided, two can be classified as bulk load operations, which have the opportunity to be minimally logged if other prerequisites have been met:

  • INSERT ... SELECT – The method for performing bulk load in process with SQL Server from local queries or any OLE DB source. This method is only ailable as a minimally logged operation in SQL Server 2008.
  • SELECT INTO – The method for creating a new table containing the results of a query; utilizes bulk load optimizations.

However, note that there are prerequisites and conditions that need to be met in order for one of these bulk load operations to be minimally logged...

If you meet these conditions, then you may see better performance by performing a bulk logged insert as described in the article...

But again, the prerequisites for this happening are pretty complex, so I would recommend reading the article before creating / changing commands with the expectation that they will be minimally logged.

EDIT:

One clarification... Note that it is the recovery model of the destination database that is relevant. For example, if you're inserting into a temporary table from tables in a database that has a full recovery model... since the temporary table resides in tempdb, which has a simple recovery model, the insert into the temporary table is a good candidate to be written as a bulk-logged operation in order to be minimally logged.

Upvotes: 2

Related Questions