Are you seeing a problem with a query or trying to out-think the optimizer? ;-)

You should trust the optimizer to find the best plan. Very very rarely has it come up with a plan that didn't perform well. It will consider all kind of things when developing the plan. Things like the amount of data, distribution of the data, the type of operators used in the constriants, available indexes and even things outside of your query like Indexed Views.

If you want more information about the optimizer, the book Inside Microsoft SQL Server 2000 does a really good job of describing the optimizer and how SQL Server "thinks."

-Mike

>To all,
>
>In Visual FoxPro, you can "force" the order in which a set of tables are joined. For example, with a hierarchicaly structured set of tables (say the classic Customers, Order, Line Items) you can force the order in which the tables are joined:
>
>SELECT * ;
>FROM Line_Items ;
>WHERE li_OrderNum IN ;
>(SELECT O.or_OrderNum ;
>FROM FORCE Customers C ;
>INNER JOIN Order O ;
>ON O.or_CustNum = C.cs_CustNum ;
>WHERE C.cs_CustName = 'FRED')
>
>In this example, the join order of the sub select starts with Customers
>(which is filtered) and goes to the Order table.
>
>With larger queries, I've seen a 25% improvement in performance using the VFP "FORCE" clause.
>
>Does T-SQL have an analog to VFP's 'FORCE' clause?
>
>Regards,
>Thom C.