Queries for NOLOCK Table Hint

1. Complex Reporting with Aggregations and Joins

SQL Server Query 1

            
 SELECT  

     c.CategoryName,  

     SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalSalesValue  

 FROM Categories c WITH (NOLOCK)  

 JOIN Products p WITH (NOLOCK) ON c.CategoryID = p.CategoryID  

 JOIN [Order Details] od WITH (NOLOCK) ON p.ProductID = od.ProductID  

 JOIN Orders o WITH (NOLOCK) ON od.OrderID = o.OrderID  

 GROUP BY c.CategoryName  

 ORDER BY TotalSalesValue DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("c.CategoryName")  

     .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

         .StartBracket(1).SUBTRACT("od.Discount").EndBracket()), "TotalSalesValue")  

     .From("Categories", "c", new List<IHint>() { new NOLOCK() })  

     .Join(new List<IJoin>()   

     {   

         new INNERJOIN().TableName("Products","p", new List<IHint>() { new NOLOCK() })  

             .On(new Column("c.CategoryID").Equale(new Column("p.CategoryID"))),  

         new INNERJOIN().TableName("[Order Details]", "od", new List<IHint>() { new NOLOCK() })  

             .On(new Column("p.ProductID").Equale(new Column("od.ProductID"))),  

         new INNERJOIN().TableName("Orders", "o", new List<IHint>() { new NOLOCK() })  

             .On(new Column("od.OrderID").Equale(new Column("o.OrderID")))  

     })  

     .GroupBy(new GroupBy("c.CategoryName"))  

     .OrderBy(new OrderBy().SetColumnDescending("TotalSalesValue"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CategoryName,
       SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507200145502961200 - od.Discount)) AS TotalSalesValue
FROM Categories AS c WITH (NOLOCK)
     INNER JOIN
     Products AS p WITH (NOLOCK)
     ON c.CategoryID = p.CategoryID
     INNER JOIN
     [Order Details] AS od WITH (NOLOCK)
     ON p.ProductID = od.ProductID
     INNER JOIN
     Orders AS o WITH (NOLOCK)
     ON od.OrderID = o.OrderID
GROUP BY c.CategoryName
ORDER BY TotalSalesValue DESC;


            
        

Parameters (If used)

Query Results 1:

2. Checking Product Stock and Order Status during Peak Hours

SQL Server Query 2

            
 SELECT  

     s.CompanyName AS Supplier,  

     SUM(p.UnitsInStock) AS TotalUnitsInStock,  

     SUM(p.UnitsOnOrder) AS TotalUnitsOnOrder  

 FROM Suppliers s WITH (NOLOCK)  

 JOIN Products p WITH (NOLOCK) ON s.SupplierID = p.SupplierID  

 GROUP BY s.CompanyName  

 ORDER BY s.CompanyName;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("s.CompanyName","Supplier")  

     .Column(new SUM(new Column("p.UnitsInStock")), "TotalUnitsInStock")  

     .Column(new SUM(new Column("p.UnitsOnOrder")), "TotalUnitsOnOrder")  

     .From("Suppliers","s", new List<IHint>() { new NOLOCK() })  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Products","p", new List<IHint>() { new NOLOCK() })  

             .On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))  

     })  

     .GroupBy(new GroupBy("s.CompanyName"))  

     .OrderBy(new OrderBy().SetColumnAscending("s.CompanyName"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT s.CompanyName AS Supplier,
       SUM(p.UnitsInStock) AS TotalUnitsInStock,
       SUM(p.UnitsOnOrder) AS TotalUnitsOnOrder
FROM Suppliers AS s WITH (NOLOCK)
     INNER JOIN
     Products AS p WITH (NOLOCK)
     ON s.SupplierID = p.SupplierID
GROUP BY s.CompanyName
ORDER BY s.CompanyName ASC;


            
        

Parameters (If used)

Query Results 2:

3. Using NOLOCK in a Subquery for "Approximate" Comparisons

SQL Server Query 3

            
 SELECT  

     p.ProductName,  

     p.UnitPrice,  

     c.CategoryName,  

     (SELECT AVG(p2.UnitPrice) FROM Products p2 WITH (NOLOCK) WHERE p2.CategoryID = p.CategoryID) AS AverageCategoryUnitPrice  

 FROM Products p WITH (NOLOCK)  

 JOIN Categories c WITH (NOLOCK) ON p.CategoryID = c.CategoryID  

 WHERE p.UnitPrice > (SELECT AVG(p3.UnitPrice) FROM Products p3 WITH (NOLOCK) WHERE p3.CategoryID = p.CategoryID) * 1.5 -- 50% higher than category average  

 ORDER BY p.UnitPrice DESC;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductName","p.UnitPrice","c.CategoryName")  

     .Column(new SqlQueryBuilder().Select()  

         .Column(new AVG(new Column("p2.UnitPrice"))).From("Products","p2", new List<IHint>() { new NOLOCK() })  

         .Where(new Where(new Column("p2.CategoryID").Equale(new Column("p.CategoryID"))))  

             , "AverageCategoryUnitPrice")  

     .From("Products", "p", new List<IHint>() { new NOLOCK() })  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Categories", "c", new List<IHint>() { new NOLOCK() })  

             .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID")))  

     })  

     .Where(new Where(new Column("p.UnitPrice").GreaterThan(  

         new ColumnArithmatic().StartBracket(  

             new SqlQueryBuilder().Select()  

             .Column(new AVG(new Column("p3.UnitPrice")))  

             .From("Products","p3", new List<IHint>() { new NOLOCK() })  

             .Where(new Where(new Column("p3.CategoryID").Equale(new Column("p.CategoryID")))))  

         .MULTIPLY(1.5)  

     )))  

     .OrderBy(new OrderBy().SetColumnDescending("p.UnitPrice"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT p.ProductName,
       p.UnitPrice,
       c.CategoryName,
       (SELECT AVG(p2.UnitPrice)
        FROM Products AS p2 WITH (NOLOCK)
        WHERE p2.CategoryID = p.CategoryID) AS AverageCategoryUnitPrice
FROM Products AS p WITH (NOLOCK)
     INNER JOIN
     Categories AS c WITH (NOLOCK)
     ON p.CategoryID = c.CategoryID
WHERE p.UnitPrice > (SELECT AVG(p3.UnitPrice)
                     FROM Products AS p3 WITH (NOLOCK)
                     WHERE p3.CategoryID = p.CategoryID) * @pMAIN_2507200145503280090
ORDER BY p.UnitPrice DESC;


            
        

Parameters (If used)

Query Results 3:

4. Using multi table hints (to be used under transaction)

SQL Server Query 4

            
 BEGIN TRANSACTION;  

 SELECT  

     OD.OrderID,  

     OD.ProductID,  

     OD.Quantity,  

     OD.UnitPrice,  

     OD.Discount  

 FROM [Order Details] AS OD WITH (NOLOCK, INDEX(PK_Order_Details)) -- Read uncommitted, force clustered index scan  

 	INNER JOIN Orders O ON O.OrderID = OD.OrderID  

 WHERE O.OrderDate >= DATEADD(day, -10000, GETDATE()); -- Assuming OrderDate is in [Order Details] or join to Orders for it  

 ROLLBACK TRANSACTION  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OD.OrderID","OD.ProductID","OD.Quantity","OD.UnitPrice","OD.Discount")  

     .From("[Order Details]","OD", new List<IHint>() { new NOLOCK(), new INDEX().SetValues("PK_Order_Details") })  

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .Where(new Where(new Column("O.OrderDate").GreaterThanOrEqualeTo(new DATEADD(SqlDateInterval.day, -10000, new GETDATE()))))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT OD.OrderID,
       OD.ProductID,
       OD.Quantity,
       OD.UnitPrice,
       OD.Discount
FROM [Order Details] AS OD WITH (NOLOCK, INDEX (PK_Order_Details))
     INNER JOIN
     Orders AS O
     ON O.OrderID = OD.OrderID
WHERE O.OrderDate >= DATEADD(day, @pMAIN_2507200145503521880, GETDATE());


            
        

Parameters (If used)

Query Results 4: