Advanced Query Tuning Hints and Plan Guides - Microsoft SQL Server 2012 Performance Tuning - page 359

Click on the Execution plan tab at the bottom of the result pane and observe
the execution plan. As it can been seen from the following screenshot, the query
optimizer has chosen an index scan operation to retrieve records:

3.

4. Now, execute the following query to retrieve the same set of records, but this time

using the FORCESEEK and INDEX table hints along with the index name, as
shown next:
USE AdventureWorks2012
GO

--Retrieving Data from Sales.SalesOrderDetail
--table for ProductID 800,801,802,803,804,805
--using FORCESEEK and INDEX query hints
SELECT
SalesOrderID
,SalesOrderDetailID
,ProductID
,OrderQty
,UnitPrice
,UnitPriceDiscount
,LineTotal
FROM Sales.SalesOrderDetail WITH (INDEX(IX_SalesOrderDetail_
ProductID), FORCESEEK)
WHERE ProductID >= 800 AND ProductID <=805

GO