Introduces and groups items in a partial abstract plan.
( hints ( derived_table )
...
)
is one or more expressions that generate a derived table.
A derived table.
select *
from t1, t2
where c12 = c21
and c11 > 0
and c22 < 1000
( hints
( g_join
( t_scan t2 )
( i_scan () t1 )
)
)
Specifies a partial plan, including a table scan on t2, the use of some index on t1, and the join order t1-t2. The index choice for t1 and the type of join (nested-loop or sort-merge) is left to the optimizer.
The specified hints are used during query optimization.
The hints operator appears as the root of a partial abstract plan that includes multiple steps. If a partial plan contains only one expression, hints is optional.
The hints operator does not appear in plans generated by the optimizer; these are always full plans.
Hints can be associated with queries:
By changing the plan for an existing query with sp_set_qplan.
By specifying the plan for a query with the plan clause. To save the query and hints, set plan dump must be enabled.
By using the create plan command.
When hints are specified in the plan clause for a SQL statement, the plans are checked to be sure they are valid. When hints are specified using sp_set_qplan, plans are not checked before being saved.
