SELECT <columnlist>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
OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
How OPTIMIZE FOR UNKNOWN Works