TABLOCK to optimize transaction logging for insert select statement

Question

I read that with the help of table tab table

For type expression

INSERT INTO Tab1 SELECT * FROM Tab2 

can optimize transaction logging.

I don’t understand this, because Tablock lock the entire table so that other processes cannot access it, so how would this optimize transaction logging?

Answer 1

Without a hint, TABLOCK SQL Server will use its usual row-level lock, so for every row it tries to insert, it will block the insertion of this new row and then continue. This can lead to a large number of locks that need to be held and managed.

Using TABLOCK simply locks the entire table for the process, so while the INSERT ... SELECT ... running, no other transactions can access the table - but there is only one lock (on the table) in place, so the amount of overhead management is much less compared to having dozens, hundreds or even thousands of individual locks for newly inserted rows.