SELECT with OPTION Hints

1. Complex Sales Report with Forced Join Order and Type

SQL Server Query 1

            
 -- Advanced Query: Complex Sales Report with Forced Join Order and Type  

 SELECT  

     C.CompanyName,  

     O.OrderDate,  

     P.ProductName,  

     OD.Quantity,  

     OD.UnitPrice,  

     (OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS LineTotal,  

     Cat.CategoryName  

 FROM Customers AS C  

 JOIN Orders AS O ON C.CustomerID = O.CustomerID  

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

 JOIN Products AS P ON OD.ProductID = P.ProductID  

 JOIN Categories AS Cat ON P.CategoryID = Cat.CategoryID  

 WHERE O.OrderDate BETWEEN '1997-01-01' AND '1997-12-31'  

   AND C.Country = 'USA'  

   AND Cat.CategoryName IN ('Beverages', 'Confections')  

 OPTION (FORCE ORDER, HASH JOIN); -- Force specific join order and HASH join algorithm  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("C.CompanyName","O.OrderDate","P.ProductName","OD.Quantity","OD.UnitPrice")  

     .Column(new ColumnArithmatic(new Column("OD.Quantity")).MULTIPLY(new Column("OD.UnitPrice"))  

         .MULTIPLY().StartBracket(1).SUBTRACT(new Column("OD.Discount")).EndBracket(), "LineTotal")  

     .Column("Cat.CategoryName")  

     .From("Customers","C")  

     .Join(new List<IJoin>()   

     {  

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

             .On(new Column("C.CustomerID").Equale(new Column("O.CustomerID"))),  

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

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

         new INNERJOIN().TableName("Products", "P")  

             .On(new Column("OD.ProductID").Equale(new Column("P.ProductID"))),  

         new INNERJOIN().TableName("Categories", "Cat")  

             .On(new Column("P.CategoryID").Equale(new Column("Cat.CategoryID")))  

     })  

     .Where(new Where(new BETWEEN(new Column("O.OrderDate"), "1997-01-01", "1997-12-31"))  

         .AND(new Column("C.Country").Equale("USA"))  

         .AND(new IN(new Column("Cat.CategoryName"), "Beverages", "Confections")))  

     .Option(new List<IOption>()   

     {  

         new FORCE_ORDER(), new HASH_JOIN()  

     })  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT C.CompanyName,
       O.OrderDate,
       P.ProductName,
       OD.Quantity,
       OD.UnitPrice,
       OD.Quantity * OD.UnitPrice * (@pMAIN_2507200213501924010 - OD.Discount) AS LineTotal,
       Cat.CategoryName
FROM Customers AS C
     INNER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
     INNER JOIN
     Products AS P
     ON OD.ProductID = P.ProductID
     INNER JOIN
     Categories AS Cat
     ON P.CategoryID = Cat.CategoryID
WHERE O.OrderDate BETWEEN @pMAIN_2507200213501924011 AND @pMAIN_2507200213501924012
      AND C.Country = @pMAIN_2507200213501924013
      AND Cat.CategoryName IN (@pMAIN_2507200213501924014, @pMAIN_2507200213501924015)
OPTION (FORCE ORDER, HASH JOIN);


            
        

Parameters (If used)

Query Results 1:

2. Advanced Query: Get Latest 100 Orders Quickly

SQL Server Query 2

            
 -- Advanced Query: Get Latest 100 Orders Quickly  

 SELECT TOP 100  

     O.OrderID,  

     O.OrderDate,  

     C.CompanyName,  

     O.Freight  

 FROM Orders AS O  

 JOIN Customers AS C ON O.CustomerID = C.CustomerID  

 WHERE O.ShippedDate IS NOT NULL -- Focus on shipped orders  

 ORDER BY O.OrderDate DESC, O.OrderID DESC  

 OPTION (FAST 100); -- Optimize for returning the first 100 rows quickly  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(100)  

     .Columns("O.OrderID","O.OrderDate","C.CompanyName","O.Freight")  

     .From("Orders","O")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Customers","C")  

             .On(new Column("O.CustomerID").Equale(new Column("C.CustomerID")))  

     })  

     .Where(new Where(new IS_NOT_NULL(new Column("O.ShippedDate"))))  

     .OrderBy(new OrderBy().SetColumnDescending("O.OrderDate")  

         .SetColumnDescending("O.OrderID"))  

     .Option(new List<IOption>()   

     {  

         new FAST(100)  

     })  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 100 O.OrderID,
               O.OrderDate,
               C.CompanyName,
               O.Freight
FROM Orders AS O
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE O.ShippedDate IS NOT NULL
ORDER BY O.OrderDate DESC, O.OrderID DESC
OPTION (FAST 100);


            
        

Parameters (If used)

Query Results 2:

3. Use HOLDLOCK to ensure SERIALIZABLE behavior on the tables (to be used in transaction)

SQL Server Query 3

            
 -- Advanced Query: Consistent Report with Strict Locking and Join Hint  

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Base isolation  

 BEGIN TRANSACTION;  

 -- Use HOLDLOCK to ensure SERIALIZABLE behavior on these tables  

 -- and INDEX hints for optimizer guidance.  

 -- The MERGE join hint is typically a QUERY hint, but we'll show how the table hints  

 -- contribute to conditions where MERGE is favorable.  

 SELECT  

     C.CustomerID,  

     C.CompanyName,  

     O.OrderID,  

     O.OrderDate,  

     O.Freight,  

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

 FROM Customers AS C WITH (HOLDLOCK, INDEX(IX_Customers_CustomerID))  

 JOIN Orders AS O WITH (HOLDLOCK, INDEX(IX_Orders_CustomerID))  

     ON C.CustomerID = O.CustomerID  

 JOIN Employees AS E WITH (HOLDLOCK, INDEX(PK_Employees)) -- PK_Employees is clustered index  

     ON O.EmployeeID = E.EmployeeID  

 ORDER BY C.CustomerID, O.OrderDate  

 OPTION (MERGE JOIN); -- Query hint for join strategy  

 ROLLBACK TRANSACTION  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("C.CustomerID","C.CompanyName","O.OrderID","O.OrderDate","O.Freight")  

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

     .From("Customers","C", new List<IHint>() { new HOLDLOCK(), new INDEX().SetValues("IX_Customers_CustomerID") })  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Orders", "O", new List<IHint>(){new HOLDLOCK(), new INDEX().SetValues("IX_Orders_CustomerID") })  

             .On(new Column("C.CustomerID").Equale(new Column("O.CustomerID"))),  

         new INNERJOIN().TableName("Employees", "E", new List<IHint>() { new HOLDLOCK(), new INDEX().SetValues("PK_Employees") })  

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

     })  

     .OrderBy(new OrderBy().SetColumnAscending("C.CustomerID")  

         .SetColumnAscending("O.OrderDate"))  

     .Option(new List<IOption>() { new MERGE_JOIN() })  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT C.CustomerID,
       C.CompanyName,
       O.OrderID,
       O.OrderDate,
       O.Freight,
       CONCAT(E.FirstName, @pMAIN_2507200213502232100, E.LastName) AS EmployeeName
FROM Customers AS C WITH (HOLDLOCK, INDEX (IX_Customers_CustomerID))
     INNER JOIN
     Orders AS O WITH (HOLDLOCK, INDEX (IX_Orders_CustomerID))
     ON C.CustomerID = O.CustomerID
     INNER JOIN
     Employees AS E WITH (HOLDLOCK, INDEX (PK_Employees))
     ON O.EmployeeID = E.EmployeeID
ORDER BY C.CustomerID ASC, O.OrderDate ASC
OPTION (MERGE JOIN);


            
        

Parameters (If used)

Query Results 3:

4. Get Orders for a Customer with RECOMPILE (e.g., for a stored procedure)

SQL Server Query 4

            
 -- Advanced Query: Get Orders for a Customer with RECOMPILE (e.g., for a stored procedure)  

 -- Simulate this in a stored procedure or an ad-hoc query that runs often.  

 SELECT  

     O.OrderID,  

     O.OrderDate,  

     O.ShipName,  

     O.Freight  

 FROM Orders AS O  

 WHERE O.CustomerID = 'ALFKI'  

 ORDER BY O.OrderDate DESC  

 OPTION (RECOMPILE); -- Force recompilation on every execution