Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?

Question

The documentation lists the following requirements for bulk imports with minimal logging

• The recovery model of the database is set to simple or bulk-logged.
• The target table is an empty or non-empty heap.
• The target table is not used in replication.
• The TABLOCK hint is specified for the target table.

(emphasis mine).

Notice that none of the above mentions B-trees. Despite this, both my experience and Iztik Ben-Gann's T-Sql Querying book claim that empty TABLOCKed B-trees will get bulk inserts.

Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree? If not, then I cannot fathom why the documentation is seemingly silent on the B-tree behaviour.

Answer 1

See if this provides any more context: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver16

It's possible that the documentation emphasizes the behaviour for heaps because they are more likely to benefit from minimal logging in the context of bulk inserts. In contrast, clustered indexes (B-trees) inherently have more complex structures and may require more extensive logging even with TABLOCK.

The behaviour of minimal logging during bulk inserts in SQL Server depends on several factors, including the presence of a clustered index and whether the table is empty or non-empty:

Heap (Empty or Non-empty): When inserting into an empty heap with TABLOCK, SQL Server can use minimal logging, even if the database is not in the BULK_LOGGED or SIMPLE recovery model. This is because the data is being appended to the end of the file and does not require individual log records for each row. When inserting into a non-empty heap with TABLOCK, minimal logging is also used. However, there may be some additional logging due to page splits if the data needs to be inserted in the middle of the heap.

Clustered Index (B-tree): If the target table has an empty clustered index, both data and index pages are minimally logged. This means that only the allocation pages and minimal log records for the extents used will be generated, resulting in reduced logging. However, if a table has a B-tree-based clustered index and is non-empty, both data pages and index pages are fully logged regardless of the recovery model. This is because inserting into a non-empty clustered index involves rearranging existing data to maintain the sorted order, which requires additional logging.

The documentation emphasizes that with an empty clustered index, both data and index pages are minimally logged, providing an efficient way to perform bulk inserts with reduced logging. This behaviour is specific to the combination of a clustered index and an empty table.

While TABLOCK can reduce logging for both heaps and clustered indexes, the behaviour is more pronounced for heaps, especially when they are empty. For clustered indexes, there may be additional logging due to the index structure.