Queries for READCOMMITTED Table Hint

1. Usage of READCOMMITTED table hint

SQL Server Query 1

            
 SELECT  

     O.OrderID,  

     OD.ProductID,  

     SUM(OD.Quantity * OD.UnitPrice) AS LineTotal  

 INTO #TempOrderSummary  

 FROM Orders AS O  

 JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID  

 WHERE O.OrderDate >= '1997-01-01'  

 GROUP BY O.OrderID, OD.ProductID;  

 SELECT  

     TS.OrderID,  

     TS.LineTotal,  

     E.FirstName + ' ' + E.LastName AS EmployeeName  

 FROM #TempOrderSummary AS TS  

 JOIN Orders AS O ON TS.OrderID = O.OrderID  

 JOIN Employees AS E WITH (READCOMMITTED) -- Force READ COMMITTED behavior for Employees table  

     ON O.EmployeeID = E.EmployeeID  

 WHERE TS.LineTotal > 1000;  

 DROP TABLE #TempOrderSummary;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID", "OD.ProductID")  

     .Column(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")), "LineTotal")  

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

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("[Order Details]","OD")  

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID")))  

     })  

     .Where(new Where(new Column("O.OrderDate").GreaterThanOrEqualeTo("1997-01-01")))  

     .GroupBy(new GroupBy("O.OrderID", "OD.ProductID"))  

     .Select()  

     .Columns("TS.OrderID", "TS.LineTotal")  

     .Column(new CONCAT(new Column("E.FirstName"), " ", new Column("E.LastName")), "EmployeeName")  

     .From("#TempOrderSummary","TS")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Orders","O")  

             .On(new Column("TS.OrderID").Equale(new Column("O.OrderID"))),  

         new INNERJOIN().TableName("Employees","E", new List<IHint>(){ new READCOMMITTED() })  

             .On(new Column("O.EmployeeID").Equale(new Column("E.EmployeeID")))  

     })  

     .Where(new Where(new Column("TS.LineTotal").GreaterThan(1000)))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT O.OrderID,
       OD.ProductID,
       SUM(OD.Quantity * OD.UnitPrice) AS LineTotal
INTO #TempOrderSummary
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
WHERE O.OrderDate >= @pMAIN_2507200138403629890
GROUP BY O.OrderID, OD.ProductID;

SELECT TS.OrderID,
       TS.LineTotal,
       CONCAT(E.FirstName, @pMAIN_2507200138403629891, E.LastName) AS EmployeeName
FROM #TempOrderSummary AS TS
     INNER JOIN
     Orders AS O
     ON TS.OrderID = O.OrderID
     INNER JOIN
     Employees AS E WITH (READCOMMITTED)
     ON O.EmployeeID = E.EmployeeID
WHERE TS.LineTotal > @pMAIN_2507200138403629892;

DROP TABLE #TempOrderSummary;


            
        

Parameters (If used)

Query Results 1: