Specify a table scan, index, or locking method for the query optimizer. Normally the query optimizer will pick the best optimization method without hints being specified.
Syntax
FAST number_rows
FORCE ORDER
HASH GROUP
ORDER GROUP
LOOP JOIN
MERGE JOIN
HASH JOIN
MAXDOP number_of_processors
OPTIMIZE FOR ( @variable_name = literal_constant [ ,…n ] )
PARAMETERIZATION { SIMPLE | FORCED }
RECOMPILE
ROBUST PLAN
KEEP PLAN
KEEPFIXED PLAN
EXPAND VIEWS
MAXRECURSION number
CONCAT UNION
HASH UNION
MERGE UNION
USE PLAN N'xml_plan'
Key:
FAST number_rows - Optimise for fast retrieval of the first number_rows.
FORCE ORDER - Preserve the join order indicated
HASH|ORDER GROUP - Used by GROUP BY, DISTINCT, or COMPUTE clause
LOOP|MERGE|HASH JOIN - Specify the allowable join operations
MAXDOP number - Override the max_degree_of_parallelism configuration option
OPTIMIZE FOR - Specify a local variables value
PARAMETERIZATION - Specify parameterization rules
RECOMPILE - Force the query optimizer to recompile the query plan
ROBUST PLAN - Optimise for reliability (rather than performance)
KEEP PLAN - Relax the estimated recompile threshold
KEEPFIXED PLAN - Do not recompile a query due to changes in statistics
MERGE|HASH|CONCAT UNION - How to perform UNION operations
EXPAND VIEWS - virtually disallow direct use of indexed views
(and indexes on indexed views) in the query plan.
MAXRECURSION number - Max no. of recursions allowed (0-32767)
USE PLAN N'xml_plan' - Force the query optimizer to use an existing query plan
An INSERT statement may only utilise Query hints within a nested SELECT clause (INSERT...SELECT... FROM ...).
Query hints cannot be specified in a subquery.
"The coolness of menthol, and a hint of mint” ~ Bill Nimmo (plugging Newport cigarettes)
Table Hints
SELECT
INSERT
UPDATE
DELETE
SET TRANSACTION ISOLATION LEVEL
Equivalent Oracle command: