TABLOCK for optimize transaction logging for insert select statement

Question

I was reading that using the TABLOCK table hint

For a statement like

INSERT INTO Tab1 
SELECT * FROM Tab2

can optimize transaction logging.

I don't understand this as Tablock will lock the whole table so no other processes can access it so how would this optimize transaction logging?

Answer 1

Without the TABLOCK hint, SQL Server will use its normal row-level locking - so for each row it attempts to insert, it will lock that new row being inserted, and then move on. This can add up to a lot of locks that need to be held and managed.

Using TABLOCK just locks the entire table for the process, so while that INSERT ... SELECT ... statement is running, no other transactions can access the table - but there's only a single lock (on the table) in place, so the amount of management overhead is much smaller compared to having dozen, hundreds or even thousands of individual locks on newly inserted rows.