Databases Reference
In-Depth Information
You can even combine both hints to obtain the same results, as in the next query.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WITH
(
INDEX
(
IX_SalesOrderDetail_ProductID
),
FORCESEEK
)
WHERE
ProductID
=
870
Listing 7-23.
Using
FORCESEEK
when SQL Server cannot do an Index Seek operation, as in the
following query, will not compile, and will instead return an error message.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WITH
(
FORCESEEK
)
WHERE
OrderQty
=
1
Listing 7-24.
FAST N
FAST
N
is one of the so-called goal oriented hints, as it does not indicate what physical
operators to use, but instead just specifies what goal the plan is trying to achieve. This
hint is used to optimize a query to retrieve the first N rows of results as quickly as
possible. It can help in situations where only the first few rows returned by a query are
relevant, and perhaps you won't be using the remaining records of the query at all. The
price to pay for achieving this speed is that retrieving those remaining records may take
longer than if you had used a plan without this hint. In other words, since the query is
optimized to retrieve the first N records as soon as possible, retrieving all the records
returned by the query may be very expensive.
