Mike,
The order of terms in the join condition is unlikely to matter here.
When a query involves many tables or tables with many potentially
helpful indexes, the optimizer cannot consider all potential query plans,
and the order in which the join is phrased can affect the specific plans
it will consider. Otherwise, it shouldn't matter.
For a join condition
SSA.Vendor + SSA.MachineType + SSA.Model) = s.Identifier
the index will not be used for its ordering of keys, regardless of
the column order of the index. The index may be useful if the entire
query references only these three columns of SSA and the clustered
index key of SSA, and the index rows are narrower than the table
rows.
It's possible that an index on these three columns would be useful
if it were declared in (Vendor,MachineType,Model) order and the
condition were written with a condition that referred to Vendor
alone, such as
SSA.Vendor < s.Identifier
and
SSA.Vendor + SSA.MachineType + SSA.Model) = s.Identifier
or something similar.
Steve Kass
Drew University
[quoted text, click to view] Mike Labosh wrote:
>An hour and 15 minutes after I hit send, this still has not even appeared in
>the newsgroup, so I am trying again, in case it went into a black hole:
>
>I have these three columns that I want to JOIN from TableA in this order:
>
>Vendor NVARCHAR(255) NULL,
>MachineType NVARCHAR(255) NULL,
>Model NVARCHAR(255) NULL
>
>To this column in TableB:
>
>Identifier NVARCHAR(500) NOT NULL -- has a unique constraint
>
>TableA has 52 million records in it, and it is still busy calculating the
>selectivity of values in each of these columns. I want to build a
>nonclustered composite index across the three columns in TableA.
>
>Once I determine the selectivity, I will build the composite index so that
>the most selective column is the first index key, then the next, then the
>last.
>
>Now my question is, does the order of the columns within the composite index
>affect the phrasing of JOIN & WHERE operations?
>
>For example, if it turns out that Model is the most selective, followed by
>MachineType, followed by Vendor, I would phrase the index this way:
>
>CREATE NONCLUSTERED INDEX SSAHardwareMatchingIX
> ON SSA (Model, MachineType, Vendor)
>
>Q1: Am I required to phrase the joins using the columns in the same order?
>:
>
>FROM Something s
> INNER JOIN SSA
> ON SSA.Model = s.Model
> AND SSA.MachineType = s.MachineType
> AND SSA.Vendor = s.Vendor
>
>Q2: Would the above index be useful for two stored procedures that have to
>join like this:
>
>FROM Something s
> INNER JOIN SSA
> ON (SSA.Vendor + SSA.MachineType + SSA.Model) = s.Identifier
>
>
