Queries for INDEX Table Hint

1. Forcing Multiple Indexes (Appropraite Indexes are required)

SQL Server Query 1

            
 SELECT  

     O.OrderID,  

     O.OrderDate,  

     C.CompanyName  

 FROM Orders O WITH (INDEX (CustomerID, OrderDate)) -- Forcing multiple indexes on 'Orders'  

 JOIN Customers C ON O.CustomerID = C.CustomerID  

 WHERE O.CustomerID IN ('BLONP','ANTON', 'BONAP') AND O.OrderDate BETWEEN '1997-01-01' AND '1997-06-30';  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

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

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

     .Join(new List<IJoin>()   

     {  

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

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

     })  

     .Where(new Where(new IN(new Column("O.CustomerID"), "BLONP","ANTON","BONAP"))  

         .AND(new BETWEEN(new Column("O.OrderDate"), "1997-01-01", "1997-06-30")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT O.OrderID,
       O.OrderDate,
       C.CompanyName
FROM Orders AS O WITH (INDEX (CustomerID, OrderDate))
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE O.CustomerID IN (@pMAIN_2507192023331680350, @pMAIN_2507192023331680351, @pMAIN_2507192023331680352)
      AND O.OrderDate BETWEEN @pMAIN_2507192023331680353 AND @pMAIN_2507192023331680354;


            
        

Parameters (If used)

Query Results 1:

2. Forcing a Clustered Index (Primary Key) Lookup (Appropraite Indexes are required)

SQL Server Query 2

            
 SELECT  

     CustomerID,  

     CompanyName,  

     ContactName,  

     City  

 FROM Customers WITH (INDEX (CompanyName)) -- Explicitly tell it to use the 'CompanyName' index  

 WHERE CompanyName LIKE 'A%';  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("CustomerID","CompanyName","ContactName","City")  

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

     .Where(new Where(new LIKE(new Column("CompanyName"), "A%")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CustomerID,
       CompanyName,
       ContactName,
       City
FROM Customers WITH (INDEX (CompanyName))
WHERE CompanyName LIKE @pMAIN_2507192023331764980;


            
        

Parameters (If used)

Query Results 2:

3. Forcing a Table Scan (INDEX = 0) (Appropraite Indexes are required)

SQL Server Query 3

            
 SELECT  

     ProductName,  

     UnitsInStock  

 FROM Products WITH (INDEX = 0) -- Force a table scan  

 WHERE UnitsInStock < 10;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductName","UnitsInStock")  

     .From("Products", new List<IHint>() { new INDEX().SetValues(0) })  

     .Where(new Where(new Column("UnitsInStock").LessThan(10)))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT ProductName,
       UnitsInStock
FROM Products WITH (INDEX (0))
WHERE UnitsInStock < @pMAIN_2507192023331826380;


            
        

Parameters (If used)

Query Results 3: