Databases Reference
In-Depth Information
Management Studio as the one from Listing 1-7, and execute both of them together,
capturing their execution plans.
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'
OPTION
(
FORCE
ORDER
)
Listing 1-8.
The result set returned is, of course, exactly the same in each case, but the execution plan
for the
FORCE
ORDER
query (shown in Figure 1-9), indicates that the Query Optimizer
followed the prescribed join order, and this time chose a Hash Match Join operator for
the first join.
Figure 1-9:
Execution plan using the
FORCE
ORDER
hint.
This might not seem significant, but if you compare the cost of each query, via the
Query cost (relative to the batch)
information at the top of each plan, you will see that
there might be a price to pay for overruling the Query Optimizer, as it has found the