Queries for FORCESEEK Table Hint

1. ForceSeek Indexes (Appropraite Indexes are required)

SQL Server Query 1

            
 SELECT  

     O.OrderID,  

     O.OrderDate,  

     O.Freight,  

     O.CustomerID  

 FROM Orders AS O WITH (FORCESEEK)   

 WHERE O.CustomerID = 'VINET'  

   AND O.OrderDate BETWEEN '1996-07-01' AND '1996-07-31'  

 ORDER BY O.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

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

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

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .Where(new Where(new Column("O.CustomerID").Equale("VINET"))  

         .AND(new BETWEEN(new Column("O.OrderDate"), "1996-07-01", "1996-07-31")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT O.OrderID,
       O.OrderDate,
       O.Freight,
       O.CustomerID
FROM Orders AS O WITH (FORCESEEK)
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE O.CustomerID = @pMAIN_2507192011455102070
      AND O.OrderDate BETWEEN @pMAIN_2507192011455102071 AND @pMAIN_2507192011455102072;


            
        

Parameters (If used)

Query Results 1:

2. Forcing a FORCESEEK on a Composite Predicate (Appropraite Indexes are required)

SQL Server Query 2

            
 SELECT  

     O.OrderID,  

     O.OrderDate,  

     O.Freight,  

     O.CustomerID  

 FROM Orders AS O WITH (FORCESEEK (CustomerID (CustomerID))) -- Force a seek on the CustomerID index, using the CustomerID column  

 WHERE O.CustomerID = 'VINET'  

   AND O.OrderDate BETWEEN '1996-07-01' AND '1996-07-31'  

 ORDER BY O.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

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

     .From("Orders", "O", new List<IHint>() { new FORCESEEK("CustomerID").SetValues("CustomerID") })  

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .Where(new Where(new Column("O.CustomerID").Equale("VINET"))  

         .AND(new BETWEEN(new Column("O.OrderDate"), "1996-07-01", "1996-07-31")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT O.OrderID,
       O.OrderDate,
       O.Freight,
       O.CustomerID
FROM Orders AS O WITH (FORCESEEK (CustomerID (CustomerID)))
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE O.CustomerID = @pMAIN_2507192011455233690
      AND O.OrderDate BETWEEN @pMAIN_2507192011455233691 AND @pMAIN_2507192011455233692;


            
        

Parameters (If used)

Query Results 2:

3. FORCESEEK in a Subquery for Efficient Lookup (Appropraite Indexes are required)

SQL Server Query 3

            
 SELECT  

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

     E.EmployeeID  

 FROM Employees AS E  

 WHERE E.EmployeeID IN (  

     SELECT O.EmployeeID  

     FROM Orders AS O WITH (FORCESEEK (CustomerID (CustomerID))) -- Force seek on CustomerID index in subquery  

     WHERE O.CustomerID IN (SELECT CustomerID from Customers)   

     GROUP BY O.EmployeeID  

     HAVING SUM(O.Freight) > 500  

 )  

 ORDER BY EmployeeName;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

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

     .Column("E.EmployeeID")  

     .From("Employees","E")  

     .Where(new Where(new IN(new Column("E.EmployeeID"), new SqlQueryBuilder().Select()  

         .Column("O.EmployeeID")  

         .From("Orders","O", new List<IHint>() {new FORCESEEK("CustomerID").SetValues("CustomerID") })  

         .Where(new Where(new IN(new Column("O.CustomerID"), new SqlQueryBuilder().Select()  

             .Column("CustomerID").From("Customers")  

         )))  

         .GroupBy(new GroupBy("O.EmployeeID"))  

         .Having(new Having(new SUM(new Column("O.Freight")).GreaterThan(500)))  

     )))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT CONCAT(E.FirstName, @pMAIN_2507192011455290360, E.LastName) AS EmployeeName,
       E.EmployeeID
FROM Employees AS E
WHERE E.EmployeeID IN ((SELECT O.EmployeeID
                        FROM Orders AS O WITH (FORCESEEK (CustomerID (CustomerID)))
                        WHERE O.CustomerID IN ((SELECT CustomerID
                                                FROM Customers))
                        GROUP BY O.EmployeeID
                        HAVING SUM(O.Freight) > @pMAIN_2507192011455290361))
ORDER BY EmployeeName ASC;


            
        

Parameters (If used)

Query Results 3:

4. FORCESEEK for Range Scans with a Composite Index (Implicit Seek) (Appropraite Indexes are required)

SQL Server Query 4

            
 SELECT  

     OD.OrderID,  

     OD.ProductID,  

     OD.Quantity,  

     OD.UnitPrice  

 FROM [Order Details] AS OD WITH (FORCESEEK (PK_Order_Details (OrderID, ProductID))) -- Force seek on composite PK  

 WHERE OD.OrderID = 10248  

   AND OD.ProductID BETWEEN 1 AND 200  

 ORDER BY OD.ProductID;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

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

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

     .Where(new Where(new Column("OD.OrderID").Equale(10248))  

     .AND(new BETWEEN(new Column("OD.ProductID"), 1, 200)))  

     .OrderBy(new OrderBy().SetColumnAscending("OD.ProductID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT OD.OrderID,
       OD.ProductID,
       OD.Quantity,
       OD.UnitPrice
FROM [Order Details] AS OD WITH (FORCESEEK (PK_Order_Details (OrderID, ProductID)))
WHERE OD.OrderID = @pMAIN_2507192011455471880
      AND OD.ProductID BETWEEN @pMAIN_2507192011455471881 AND @pMAIN_2507192011455471882
ORDER BY OD.ProductID ASC;


            
        

Parameters (If used)

Query Results 4: