Quick Summary for Minimally Logged Operations ( Inserts and Merge)

INSERT PERFORMANCE:

Below is the summary of options that we can use for improving insert performance.

This is assuming the database is in simple or Bulk Recovery model.

Table Indexes	Rows in table	Hints	Without TF 610	With TF 610	Concurrent possible
Heap	Any	TABLOCK	Minimal	Minimal	Yes
Heap	Any	None	Full	Full	Yes
Heap + Index	Any	TABLOCK	Full	Depends (3)	No
Cluster	Empty	TABLOCK, ORDER (1)	Minimal	Minimal	No
Cluster	Empty	None	Full	Minimal	Yes (2)
Cluster	Any	None	Full	Minimal	Yes (2)
Cluster	Any	TABLOCK	Full	Minimal	No
Cluster + Index	Any	None	Full	Depends (3)	Yes (2)
Cluster + Index	Any	TABLOCK	Full	Depends (3)	No

Table 1: Summary of minimal logging conditions

(1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used.

(2) Concurrent loads only possible under certain conditions. Also, only rows written to newly allocated pages are minimally logged.

(3) Depending on the plan chosen by the optimizer, the non-clustered index on the table may either be fully- or minimally logged.

MERGE PERFORMANCE:

The Transact-SQL MERGE command offers minimal logging of the inserted rows. Unless you run MERGE on a heap, this minimal logging ability will only be used if trace flag 610 is enabled.