Databases Reference
In-Depth Information
As you can see, the Query Optimizer does not follow the join order you have specified in
the query syntax; instead it found a better join order based on cost decisions. Now let's
see what happens if we change the query to use non-
ANSI
joins, by removing the
ON
clauses and separating the table names with commas, and finally adding a
FORCE
ORDER
hint to the query. It will produce the plan in Figure 7-9.
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
,
Sales
.
Individual
AS
I
,
Sales
.
Customer
AS
Cu
WHERE
I
.
CustomerID
=
Cu
.
CustomerID
AND
C
.
ContactID
=
I
.
ContactID
AND
Cu
.
CustomerType
=
'I'
OPTION
(
FORCE
ORDER
)
Listing 7-13.
Figure 7-9:
Execution plan with
FORCE
ORDER
hint.
In this query using non-
ANSI
joins and the
FORCE
ORDER
hint, the tables will be joined in
the order specified in the query, and by default will create a left-deep tree. On the other
hand, if you are using the
FORCE
ORDER
hint in a query
with
ANSI
joins, SQL Server will
consider the location of the
ON
clauses to define the location of the joins. As an example
of this phenomenon, the query in Listing 7-14 will create a similar plan to the one shown
in Figure 7-9 but, in this case, SQL Server
is
considering the location of the
ON
clauses
