Tablock Table Hint in SQL Server

The TABLOCK table hint in SQL Server instructs the query optimizer to take a table level lock instead of row level or page level locks. This can be useful for certain operations particularly bulk inserts or large data modification where table level locking can improve performance by reducing the row or page locks. It is often used with insert and update and delete statement.

A key benefit of the TABLOCK hint is that it reduces the amount of log space used during the operation, as the minimal logging mechanism is activated. This means that SQL Server does not have to log each individual row insertion, but rather just the metadata for the bulk operation. As a result, this significantly reduces the overhead and speeds up data loading.

Additionally, because the table is locked at the schema level, it allows SQL Server to parallelize the operation, leading to faster execution times. This is particularly useful for large-scale data-loading tasks.

Benefit of TABLOCK

·   Improved Performance: The primary benefit of using the TABLOCK hint is the performance improvement during large INSERT operations. By reducing the amount of logging, SQL Server can insert rows much faster.

·  Parallel Insertion: With TABLOCK, SQL Server can use parallelism to load the data, speeding up the operation of systems with sufficient resources.

·   Reduced Logging Overhead: Since SQL Server logs less information, the system consumes less log space, which can be crucial when working with large datasets.

·   Works with Temp Tables: You can apply TABLOCK to temporary tables as well, giving you the same performance benefits for in-memory operations.

Let’s see the example below.

Here we are creating a table and inserting the records into this table.

CREATE TABLE Sales.SalesOrderDetail_Temp(               SalesOrderID int NOT NULL,               SalesOrderDetailID int NOT NULL,               CarrierTrackingNumber nvarchar(25) NULL,               OrderQty smallint NOT NULL,               ProductID int NOT NULL,               SpecialOfferID int NOT NULL,               UnitPrice money NOT NULL,               UnitPriceDiscount money NOT NULL,               LineTotal decimal(18,10),               rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,               ModifiedDate datetime NOT NULL               )

 Let’s insert the records in the table without any table hint.

SET STATISTICS TIME, IO ON SET NOCOUNT ON INSERT INTO Sales.SalesOrderDetail_Temp SELECT     SalesOrderID               ,SalesOrderDetailID               ,CarrierTrackingNumber               ,OrderQty               ,ProductID               ,SpecialOfferID               ,UnitPrice               ,UnitPriceDiscount               ,LineTotal                ,rowguid               ,ModifiedDate FROM Sales.SalesOrderDetail;

 

Let’s use TABLOCK table hint.

Before that we are truncating this table.

Truncate table Sales.SalesOrderDetail_Temp

After truncate table loading the data into this table.

SET STATISTICS TIME, IO ON SET NOCOUNT ON INSERT INTO Sales.SalesOrderDetail_Temp WITH (TABLOCK) SELECT     SalesOrderID               ,SalesOrderDetailID               ,CarrierTrackingNumber               ,OrderQty               ,ProductID               ,SpecialOfferID               ,UnitPrice               ,UnitPriceDiscount               ,LineTotal                ,rowguid               ,ModifiedDate FROM Sales.SalesOrderDetail;

 

See the elapsed time in both. Using hits performance is good.