Queries for FORCESEEK Table Hint

1. FORCESCAN with a Join (Influencing Scan vs. Seek for a Table in a Join)

SQL Server Query 1

            
 SELECT  

     C.CompanyName,  

     COUNT(O.OrderID) AS NumberOfOrders  

 FROM Customers AS C WITH (FORCESCAN) -- Force a scan on the Customers table  

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

 WHERE O.EmployeeID = 1 -- Filter for a specific employee  

 GROUP BY C.CompanyName  

 ORDER BY NumberOfOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("C.CompanyName")  

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

     .From("Customers", "C", new List<IHint>() { new FORCESCAN() })  

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .Where(new Where(new Column("O.EmployeeID").Equale(1))) 

     .GroupBy(new GroupBy("C.CompanyName"))  

     .OrderBy(new OrderBy().SetColumnDescending("NumberOfOrders"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT C.CompanyName,
       COUNT(O.OrderID) AS NumberOfOrders
FROM Customers AS C WITH (FORCESCAN)
     INNER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
WHERE O.EmployeeID = @pMAIN_2507192014395563290
GROUP BY C.CompanyName
ORDER BY NumberOfOrders DESC;


            
        

Parameters (If used)

Query Results 1:

2. FORCESCAN with GROUP BY and Aggregations on a Large Table

SQL Server Query 2

            
 SELECT  

     OD.ProductID,  

     SUM(OD.Quantity) AS TotalQuantityOrdered,  

     AVG(OD.Discount) AS AverageDiscount  

 FROM [Order Details] AS OD WITH (FORCESCAN) -- Force a scan on the Order Details table  

 WHERE OD.Quantity > 5  

 GROUP BY OD.ProductID  

 ORDER BY TotalQuantityOrdered DESC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("OD.ProductID")  

     .Column(new SUM(new Column("OD.Quantity")),"TotalQuantityOrdered")  

     .Column(new AVG(new Column("OD.Discount")),"AverageDiscount")  

     .From("[Order Details]", "OD", new List<IHint>() { new FORCESCAN() })  

     .Where(new Where(new Column("OD.Quantity").GreaterThan(5)))  

     .GroupBy(new GroupBy("OD.ProductID"))  

     .OrderBy(new OrderBy().SetColumnDescending("TotalQuantityOrdered"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT OD.ProductID,
       SUM(OD.Quantity) AS TotalQuantityOrdered,
       AVG(OD.Discount) AS AverageDiscount
FROM [Order Details] AS OD WITH (FORCESCAN)
WHERE OD.Quantity > @pMAIN_2507192014395792120
GROUP BY OD.ProductID
ORDER BY TotalQuantityOrdered DESC;


            
        

Parameters (If used)

Query Results 2:

3. FORCESCAN to Avoid Complex Predicate Evaluations or SARGability Issues

SQL Server Query 3

            
 SELECT  

     O.OrderID,  

     O.OrderDate,  

     DATENAME(weekday, O.OrderDate) AS OrderWeekday  

 FROM Orders AS O WITH (FORCESCAN) -- Force a scan (likely on OrderDate index or clustered index)  

 WHERE DATENAME(weekday, O.OrderDate) = 'Monday'  

 ORDER BY O.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID", "O.OrderDate")  

     .Column(new DATENAME(SqlDateInterval.weekday, new Column("O.OrderDate")), "OrderWeekday")  

     .From("Orders", "O", new List<IHint>() { new FORCESCAN() })  

     .Where(new Where(new DATENAME(SqlDateInterval.weekday, new Column("O.OrderDate")).Equale("Monday")))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT O.OrderID,
       O.OrderDate,
       DATENAME(weekday, O.OrderDate) AS OrderWeekday
FROM Orders AS O WITH (FORCESCAN)
WHERE DATENAME(weekday, O.OrderDate) = @pMAIN_2507192014395876160
ORDER BY O.OrderDate ASC;


            
        

Parameters (If used)

Query Results 3:

4. FORCESCAN for a Full Table Read in a Join Context

SQL Server Query 4

            
 SELECT  

     C.CompanyName,  

     O.OrderID,  

     O.OrderDate  

 FROM Customers AS C WITH (FORCESCAN) -- Force a scan on the Customers table  

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

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

 ORDER BY C.CompanyName, O.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

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

     .From("Customers","C", new List<IHint>() { new FORCESCAN() })  

     .Join(new List<IJoin>()   

     {   

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

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

     })  

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

     .OrderBy(new OrderBy()  

         .SetColumnAscending("C.CompanyName")  

         .SetColumnAscending("O.OrderDate"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT C.CompanyName,
       O.OrderID,
       O.OrderDate
FROM Customers AS C WITH (FORCESCAN)
     INNER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
WHERE O.OrderDate BETWEEN @pMAIN_2507192014395972000 AND @pMAIN_2507192014395972001
ORDER BY C.CompanyName ASC, O.OrderDate ASC;


            
        

Parameters (If used)

Query Results 4:

5. FORCESCAN on a Large Table with a Non-Selective Join Condition

SQL Server Query 5

            
 SELECT  

     P.ProductName,  

     OD.OrderID,  

     OD.Quantity  

 FROM Products AS P  

 JOIN [Order Details] AS OD WITH (FORCESCAN)  -- Force a scan on the Order Details table  

     ON P.ProductID = OD.ProductID  

 WHERE OD.ProductID IN (1, 2, 3, 4, 5);  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("P.ProductName", "OD.OrderID", "OD.Quantity")  

     .From("Products", "P")  

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .Where(new Where(new IN(new Column("OD.ProductID"), 1, 2, 3, 4, 5)))  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT P.ProductName,
       OD.OrderID,
       OD.Quantity
FROM Products AS P
     INNER JOIN
     [Order Details] AS OD WITH (FORCESCAN)
     ON P.ProductID = OD.ProductID
WHERE OD.ProductID IN (@pMAIN_2507192014396070860, @pMAIN_2507192014396070861, @pMAIN_2507192014396070862, @pMAIN_2507192014396070863, @pMAIN_2507192014396070864);


            
        

Parameters (If used)

Query Results 5: