Databases Reference
In-Depth Information
SELECT
FirstName, LastName
FROM
Person.Contact
AS
C
JOIN Sales.Individual
AS
I
ON
C.ContactID > I.ContactID
WHERE
C.ContactID > 19974
OPTION
(
MERGE
JOIN)
Listing 7-4.
Msg 8622, Level 16, State 1, Line 2
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Listing 7-5.
However, as mentioned before, keep in mind that using the query-level hint will impact
the entire query. If you need explicit control over
each
join in a query, then you can use
ANSI
-style join hints, the benefit of which is that a join type can be individually selected
for every join in the plan. However, be warned that using
ANSI
join hints will also add the
behavior of the
FORCE
ORDER
hint, which asks to preserve the join
order
and aggregation
placement, as indicated by the query syntax. This behavior will be explained in the
FORCE
ORDER
section, later in this chapter.
In the meantime, let me show you an example. The following query without hints will
produce the execution plan shown in Figure 7-4:
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
Customer
AS
Cu
ON
I
.
CustomerID
=
Cu
.
CustomerID
WHERE
Cu
.
CustomerType
=
'I'
Listing 7-6.
