You can use the plan clause with the following SQL statements to specify the plan to use to execute the query:
select
insert...select
delete
update
if
while
return
This example specifies the plan to use to execute the query:
select avg(price) from titles
plan
" ( plan
( i_scan type_price_ix titles )
( )
)"
When you specify an abstract plan for a query, the query is executed using the specified plan. If you have showplan enabled, this message is printed:
Optimized using the Abstract Plan in the PLAN clause.
When you use the plan clause with a query, any errors in the SQL text, the plan syntax, and any mismatches between the plan and the SQL text are reported as errors. For example, this plan omits the empty parentheses that specify the step of returning the aggregate:
/* step missing! */
select avg(price) from titles
plan
" ( plan
( i_scan type_price titles )
)"
It returns the following message:
Msg 1005, Level 16, State 1: Server ‘smj’, Line 2: Abstract Plan (AP) : The number of operands of the PLAN operator in the AP differs from the number of steps needed to compute the query. The extra items will be ignored. Check the AP syntax and its correspondence to the query.
Plans specified with the plan clause are saved in sysqueryplans only if plan capture is enabled. If a plan for the query already exists in the current capture group, you must enable replace mode in order to replace an existing plan.
