Future improvements the optimizer and future join methods wont ever get applied

ChAptEr 23 ■ hintS

MERGE Merge joins are optimal for medium or large tables that are sorted on the joined column.

REMOTE This causes the join to be performed on the server hosting the table that is listed on the right-hand side of the join clause. This hint matters only when a join involves tables sitting on two different servers.

23-2. Forcing a Statement Recompile

Problem

SELECT SalesOrderID,
ProductID,
UnitPrice,
OrderQty
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = @CarrierTrackingNumber ORDER BY SalesOrderID,
ProductID
OPTION (RECOMPILE);

This returns the following:

You may decide that you want to take this recipe’s approach when faced with a query for which query plans are volatile, in which differing search-condition values for the same plan cause extreme fluctuations in the number of rows returned. In such a scenario, using a compiled query plan may hurt, not help, query performance. The benefit of a cached and reusable query execution plan (the avoided cost of compilation) may occasionally be outweighed by the actual poor performance of the query as it is executed using the saved plan.

602