OPTIMIZE FOR UNKNOWN and other query hints

Query Hints go in the OPTION clause at the end of a query.

SELECT <columnlist>
FROM <table>
WHERE <clause>
OPTION(query hint)

The only one I've really had cause to use in the past is MAXDOP 1 to prevent large queries unsuccessfully using multiple processors.

Some new ones (to me) I read about today, are...

• KEEP PLAN - Reduces likelihood of plan recompilation by relaxing the recompile threshold.
• KEEPFIXED PLAN - Tells optimiser not to recompile plan even if statistics have changed.
• ROBUST PLAN - Use plan with maximum possible rowsize.
• OPTIMIZE FOR (parameter value) - use this value as a local variable in query optimization
• OPTIMIZE FOR UNKNOWN (New to SQL 2008)- Use statistical data to determine value for local variable in query optimization

Links :
OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
How OPTIMIZE FOR UNKNOWN Works
Query Hints