Queries for TABLOCKX Table Hint

1. Usage of TABLOCKX table hint (to be used in transaction)

SQL Server Query 1

            
 BEGIN TRANSACTION;  

 -- Set Temp table  

 SELECT 0 AS ProductID, CAST('MyProduct' AS NVARCHAR(50)) AS ProductName, CAST(0.00 AS MONEY) AS UnitPrice   

 INTO #TempNewProducts  

 -- Clear Temp Table  

 DELETE FROM #TempNewProducts WHERE ProductID = 0  

 -- Perform a bulk insert with TABLOCK.  

 -- This will acquire an Exclusive (X) table lock on #TempNewProducts.  

 -- Since it's a new table and no other connections are expected,  

 -- the lock is easily granted, and the insert can proceed very efficiently.  

 INSERT INTO #TempNewProducts WITH (TABLOCKX) (ProductID, ProductName, UnitPrice)  

 SELECT ProductID + 1000, ProductName + ' (New)', UnitPrice * 1.1  

 FROM Products;  

 -- In a real scenario, you'd then merge this data into the main table  

 -- INSERT INTO Products ... SELECT FROM #TempNewProducts;  

 -- Or UPDATE Products ... FROM #TempNewProducts;  

 SELECT * FROM #TempNewProducts  

 DROP TABLE #TempNewProducts;  

 COMMIT TRANSACTION;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .ColumnWithValue(0, "ProductID")  

     .Column(new CAST("MyProduct", SqlDataType.NVARCHAR, "50"), "ProductName")  

     .Column(new CAST(0, SqlDataType.MONEY), "UnitPrice")  

     .INTO(new Table("#TempNewProducts"))  

     .DeleteFrom("#TempNewProducts").Where(new Where(new Column("ProductID").Equale(0)))  

     .InsertInto("#TempNewProducts", new List<IHint>() { new TABLOCKX() },   

         new List<string>() { "ProductID",  "ProductName", "UnitPrice" }, new SqlQueryBuilder()  

             .Select()  

             .Column(new ColumnArithmatic("ProductID").ADD(1000))  

             .Column(new CONCAT(new Column("ProductName"), " (New)"))  

             .Column(new ColumnArithmatic(new Column("UnitPrice")).MULTIPLY(1.1)))  

     .From("Products")  

     .Select().Star().From("#TempNewProducts")  

     .DropTable(new Table("#TempNewProducts"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT @pMAIN_2507200155145703030 AS ProductID,
       CAST (@pMAIN_2507200155145703031 AS NVARCHAR (50)) AS ProductName,
       CAST (@pMAIN_2507200155145703032 AS MONEY) AS UnitPrice
INTO #TempNewProducts;

DELETE #TempNewProducts
WHERE ProductID = @pMAIN_2507200155145703033;

INSERT INTO #TempNewProducts WITH (TABLOCKX) (ProductID, ProductName, UnitPrice)
SELECT ProductID + @pMAIN_2507200155145703034,
       CONCAT(ProductName, @pMAIN_2507200155145703035),
       UnitPrice * @pMAIN_2507200155145703036
FROM Products;

SELECT *
FROM #TempNewProducts;

DROP TABLE #TempNewProducts;


            
        

Parameters (If used)

Query Results 1: