Databases Reference
In-Depth Information
Join hints can not only force the joins we explicitly specify in our query text, but can
also impact most of the joins introduced by the Query Optimizer, such as foreign key
validation or cascading actions. Other joins, like the Nested Loops used in a bookmark
lookup, cannot be changed, as it would defeat the purpose of using the bookmark lookup
in the first place. For example, in the following query, the hint to use a Merge Join will be
ignored, as shown in the plan in Figure 7-3.
SELECT
AddressID
,
City
,
StateProvinceID
,
ModifiedDate
FROM
Person
.
Address
WHERE
City
=
'Santa Fe'
OPTION
(
MERGE
JOIN)
Listing 7-3.
Figure 7-3:
Hint ignored in a bookmark lookup example.
As mentioned earlier, hints cannot force the Query Optimizer to generate invalid plans,
so the query in Listing 7-4 will not compile, as both Merge and Hash Joins require an
equality operator on the join predicate. Trying to execute this query will return the error
message shown in Listing 7-5.
