WITH (TABLOCK) - Minimal logging while insert

In order to improve performance for an insert statement,

We can consider use WITH (TABLOCK) option:

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME) SELECT ...

Whet it will do is to minimize the writes to the log.

Try it:

Run insertion statement with and without the TABLOCK and check the log:

INSERT INTO dbo.testItai (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b 

Check the log using

select * from fn_dblog(null, null)

You will see that after the insertion without the TABLOCK 5000 records will be written in the log, and much less with the TABLOCK.

WITH (TABLOCK) has few constraints:

1. The destination table must be empty or without clustered index.

2. There is no non-clustered index on the destination table.