Hugo Kornelis (5/12/2010)
Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use that index for the query. If you specify to use hash joins, you WILL get hash joins. No matter how high the cost and how much cheaper an unhinted plan would have been. If you have any evidence to the contrary, please let me know - it's quite likely to be a bug.
Even "optimiser directive" doesn't quite capture it, since not all 'hints' apply to the optimiser. Some hints (like NOWAIT, REPEATABLEREAD, IGNORE_TRIGGERS) are only used by the execution engine. Other hints (like KEEPIDENTITY) materially change the effect of the query.
I prefer to think of the true optimiser hints as 'implementation rule switches', since they affect which physical implementations are available to the optimiser as it explores the plan space.
Given the wide range of effects on various stages of query execution, I think 'hint' is as good a term as any, though undoubtedly a flawed one.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi