> It *can* use that index, but the plan will still prefer the
> existing single-column index, for no good reason. In some cases,
> it won't choose either because it can't make up its mind either
> way. You can force your own plan that replaces the FK index with
> the one you prefer----provided the OrderStatus column is the
> *first* key of the special index.
>
> Another way to look at this is whether you need that foreign key at
> all. Do the Order Status "master" key values get changed and
> deleted? Are you making the maintenance of control tables
> available Uncle Tom Cobbley, Joe Bloggs and his dogs? Or do you
> protect them so that only someone who knows the effects is allowed
> to change or delete control values?
>
> /heLen

I agree that there is usually little reason to allow users
to "manage" control tables - so no, I do not allow any "joe" to start
mucking around with OrderStatus values...

However, RI is more than that... If I remove the RI (Foreign Key
constraint) between CustOrder and OrderStatus, what prevents someone
from adding a new CustOrder without a corresponding OrderStatus or an
incorrect OrderStatus... the user is not editing the Control table
but certainly our data entry people need to be able to add orders...

That is where I am getting confused, am I supposed to remove my
Foreign Key constraints, and add new indices, and then I can optimize
my queries?

Thanks for the help :-)
--Raymond