Databases Reference
In-Depth Information
Of course, you can also force the opposite operation. In the following example, the Query
Optimizer estimates that a high number of records will be returned, and so it decides to
use a Clustered Index Scan, similar to the plan previously shown in Figure 7-14.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
870
Listing 7-20.
Since we have an available index on
ProductID
(
IX_SalesOrderDetail_ProductID)
,
we can force the plan to use that index.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WITH
(
INDEX
(
IX_SalesOrderDetail_ProductID
))
WHERE
ProductID
=
870
Listing 7-21.
This query will produce a new plan, similar to the one in Figure 7-13 which, in fact, is
using an Index Seek on the
IX_SalesOrderDetail_ProductID
index, and a Key
Lookup to the base table, which in this case is the clustered index.
You can also achieve a similar result by forcing a seek using the
FORCESEEK
table hint,
which is new in SQL Server 2008. The following query will create a plan similar to the
one shown previously in Figure 7-13.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WITH
(
FORCESEEK
)
WHERE
ProductID
=
870
Listing 7-22.
