Databases Reference
In-Depth Information
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
897
Listing 7-18.
Figure 7-13:
Plan without hints.
However, suppose that you want to avoid a bookmark lookup operation; you can use the
INDEX
table hint to force a table scan instead (which could be the scan of either a heap
or a clustered index). The following query will force the use of a Clustered Index Scan
operator, as shown on the plan on Figure 7-14.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WITH
(
INDEX
(
0
))
WHERE
ProductID
=
897
Listing 7-19.
Figure 7-14:
Plan with an
INDEX
hint.
Using
INDEX(1)
in this example would give a similar result, as SQL Server cannot use the
clustered index to do an Index Seek operation; the clustered key is on
SalesOrderID
and
SalesOrderDetailID
, so the only viable choice is to scan the clustered index.
