SELECT with OPTION Hints

6. Sales Report with Forced Join Order

SQL Server Query 1

            
 -- Advanced Query: Sales Report with Forced Join Order  

 SELECT  

     C.CompanyName,  

     Cat.CategoryName,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS TotalCategorySales  

 FROM Customers AS C -- 1st in join order (outermost)  

 JOIN Orders AS O ON C.CustomerID = O.CustomerID -- 2nd  

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

 JOIN Products AS P ON OD.ProductID = P.ProductID -- 4th  

 JOIN Categories AS Cat ON P.CategoryID = Cat.CategoryID -- 5th (innermost)  

 WHERE C.Country = 'USA'  

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

 GROUP BY C.CompanyName, Cat.CategoryName  

 ORDER BY C.CompanyName, Cat.CategoryName  

 OPTION (FORCE ORDER); -- Forces the optimizer to join tables in the FROM clause order  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql6, parameters6) = new SqlQueryBuilder()  

     .Select()  

     .Columns("C.CompanyName","Cat.CategoryName")  

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

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "TotalCategorySales")  

     .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 Column("C.Country").Equale("USA"))  

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

     .GroupBy(new GroupBy("C.CompanyName","Cat.CategoryName"))  

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

         .SetColumnAscending("Cat.CategoryName"))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT C.CompanyName,
       Cat.CategoryName,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507192011455121520 - OD.Discount)) AS TotalCategorySales
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 C.Country = @pMAIN_2507192011455121521
      AND O.OrderDate >= @pMAIN_2507192011455121522
GROUP BY C.CompanyName, Cat.CategoryName
ORDER BY C.CompanyName ASC, Cat.CategoryName ASC
OPTION (FORCE ORDER);


            
        

Parameters (If used)

Query Results 1:

7. Complex Sales Report with Forced Order, Specific Joins, and Recompilation

SQL Server Query 2

            
 -- Advanced Query: Complex Sales Report with Forced Order, Specific Joins, and Recompilation  

 SELECT  

     C.CompanyName,  

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

     O.OrderDate,  

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

 FROM Customers AS C -- First table in order  

 INNER HASH JOIN Orders AS O ON C.CustomerID = O.CustomerID -- Force HASH join for C-O  

 INNER LOOP JOIN Employees AS E ON O.EmployeeID = E.EmployeeID -- Force LOOP join for O-E  

 INNER HASH JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID -- Force HASH join for O-OD  

 WHERE C.Region = 'WA'  

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

 GROUP BY C.CompanyName, E.FirstName, E.LastName, O.OrderDate  

 ORDER BY C.CompanyName, O.OrderDate  

 OPTION (FORCE ORDER, RECOMPILE); -- Force overall order and recompilation  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql7, parameters7) = new SqlQueryBuilder()  

     .Select()  

     .Column("C.CompanyName")  

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

     .Column("O.OrderDate")  

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

     .From("Customers", "C")  

     .Join(new List<IJoin>()  

     {  

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

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

         new INNERLOOPJOIN().TableName("Employees", "E")  

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

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

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

     })  

     .Where(new Where(new Column("C.Region").Equale("WA"))  

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

     .GroupBy(new GroupBy("C.CompanyName", "E.FirstName", "E.LastName", "O.OrderDate"))  

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

         .SetColumnAscending("O.OrderDate"))  

     .Option(new List<IOption>() { new FORCE_ORDER(), new RECOMPILE() })  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT C.CompanyName,
       CONCAT(E.FirstName, @pMAIN_2507192011455355530, E.LastName) AS EmployeeName,
       O.OrderDate,
       SUM(OD.Quantity * OD.UnitPrice) AS OrderLineTotal
FROM Customers AS C
     INNER HASH JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
     INNER LOOP JOIN
     Employees AS E
     ON O.EmployeeID = E.EmployeeID
     INNER HASH JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
WHERE C.Region = @pMAIN_2507192011455355531
      AND O.OrderDate >= @pMAIN_2507192011455355532
GROUP BY C.CompanyName, E.FirstName, E.LastName, O.OrderDate
ORDER BY C.CompanyName ASC, O.OrderDate ASC
OPTION (FORCE ORDER, RECOMPILE);


            
        

Parameters (If used)

Query Results 2:

8. Targeted Customer Order Analysis with LOOP JOINs

SQL Server Query 3

            
 -- Advanced Query: Targeted Customer Order Analysis with LOOP JOINs  

 SELECT  

     C.CompanyName,  

     C.City,  

     O.OrderID,  

     O.OrderDate,  

     OD.ProductID,  

     OD.Quantity,  

     OD.UnitPrice,  

     OD.Discount  

 FROM Customers AS C WITH (INDEX(IX_Customers_Region)) -- Hint to use index for region filter  

 INNER LOOP JOIN Orders AS O ON C.CustomerID = O.CustomerID -- Force LOOP JOIN between Customers and Orders  

 INNER LOOP JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID -- Force LOOP JOIN between Orders and Order Details  

 WHERE C.Region = 'WA' -- Assuming 'WA' yields a small number of customers  

 ORDER BY C.CompanyName, O.OrderDate, O.OrderID, OD.ProductID  

 OPTION (RECOMPILE); -- Optional: Force recompilation to ensure plan is fresh for this specific hint  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql8, parameters8) = new SqlQueryBuilder()  

     .Select()  

     .Columns("C.CompanyName","C.City","O.OrderID","O.OrderDate","OD.ProductID","OD.Quantity","OD.UnitPrice","OD.Discount")  

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

     .Join(new List<IJoin>()  

     {  

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

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

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

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

     })  

     .Where(new Where(new Column("C.Region").Equale("WA")))  

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

         .SetColumnAscending("O.OrderDate")  

         .SetColumnAscending("O.OrderID")  

         .SetColumnAscending("OD.ProductID"))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT C.CompanyName,
       C.City,
       O.OrderID,
       O.OrderDate,
       OD.ProductID,
       OD.Quantity,
       OD.UnitPrice,
       OD.Discount
FROM Customers AS C WITH (INDEX (IX_Customers_Region))
     INNER LOOP JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
     INNER LOOP JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
WHERE C.Region = @pMAIN_2507192011455577060
ORDER BY C.CompanyName ASC, O.OrderDate ASC, O.OrderID ASC, OD.ProductID ASC
OPTION (RECOMPILE);


            
        

Parameters (If used)

Query Results 3:

9. Advanced Query: Employee-Customer Sales Aggregation with HASH GROUP Hint

SQL Server Query 4

            
 -- Advanced Query: Employee-Customer Sales Aggregation with HASH GROUP Hint  

 SELECT TOP 50  

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

     C.CompanyName,  

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

     COUNT(DISTINCT O.OrderID) AS NumberOfOrders  

 FROM Employees AS E  

 JOIN Orders AS O ON E.EmployeeID = O.EmployeeID  

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

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

 -- Adding a broad WHERE clause to simulate large input for aggregation, if needed  

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

 GROUP BY E.FirstName, E.LastName, C.CompanyName  

 ORDER BY EmployeeName, CompanyName -- Keep ORDER BY, but HASH GROUP might still be chosen before final sort  

 OPTION (HASH GROUP); -- Explicitly hint the optimizer to prefer Hash Aggregate  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql9, parameters9) = new SqlQueryBuilder()  

     .Select().Top(50)  

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

     .Column("C.CompanyName")  

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

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "TotalSalesValue")  

     .Column(new COUNT(new Column("O.OrderID"), true), "NumberOfOrders")  

     .From("Employees","E")  

     .Join(new List<IJoin>()  

     {  

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

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

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

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

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

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

     })  

     .GroupBy(new GroupBy("E.FirstName", "E.LastName", "C.CompanyName"))  

     .OrderBy(new OrderBy().SetColumnAscending("EmployeeName")  

         .SetColumnAscending("CompanyName"))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT TOP 50 CONCAT(E.FirstName, @pMAIN_2507192011455705200, E.LastName) AS EmployeeName,
              C.CompanyName,
              SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507192011455705201 - OD.Discount)) AS TotalSalesValue,
              COUNT(DISTINCT O.OrderID) AS NumberOfOrders
FROM Employees AS E
     INNER JOIN
     Orders AS O
     ON E.EmployeeID = O.EmployeeID
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY E.FirstName, E.LastName, C.CompanyName
ORDER BY EmployeeName ASC, CompanyName ASC
OPTION (MERGE JOIN);


            
        

Parameters (If used)

Query Results 4: