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

Getting ready

In this recipe, we first see that certain ProductIDs cause an index scan operation while
retrieving data from the Sales.SalesOrderDetail table. Then we will use the query
hints INDEX and FORCESEEK , so that an index seek operation can be used.

Before starting this recipe, make sure that you fulfil the following prerequisites:

F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
F Sample AdventureWorks2012 database on the instance of SQL Server

How to do it...

Follow the ensuing steps to perform this recipe:

1. Start SQL Server Management Studio and connect to SQL Server.

2. In a new query window, execute the following T-SQL script against the

AdventureWorks2012 database. Make sure that, before executing a query,
you click on Include Actual Execution Plan to view the execution plan once
query execution is completed:
USE AdventureWorks2012
GO

--Retrieving Data from Sales.SalesOrderDetail table
-- for ProductID 800,801,802,803,804,805
SELECT
SalesOrderID
,SalesOrderDetailID
,ProductID
,OrderQty
,UnitPrice
,UnitPriceDiscount
,LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID >= 800 AND ProductID <=805

GO