Query Hint

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)

Related commands:

Table Hints
SELECT
INSERT
UPDATE

DELETE
SET TRANSACTION ISOLATION LEVEL

Equivalent Oracle command:

Oracle Hints


© Copyright SS64.com 1999-2014
Some rights reserved