The create plan command specifies the text of a query, and the abstract plan to save for the query.
This example creates an abstract plan:
create plan
"select avg(price) from titles"
"( plan
( i_scan type_price_ix titles )
( )
)"
The plan is saved in the current active plan group. You can also specify the group name:
create plan
"select avg(price) from titles"
"( plan
( i_scan type_price_ix titles )
( )
)"
into dev_plans
If a plan already exists for the specified query in the current plan group, or the plan group that you specify, you must first enable replace mode in order to overwrite the existing plan.
If you want to see the plan ID that is used for a plan you create, create plan can return the ID as a variable. You must declare the variable first. This example returns the plan ID:
declare @id int
create plan
"select avg(price) from titles"
"( plan
( i_scan type_price_ix titles )
( )
)"
into dev_plans
and set @id
select @id
When you use create plan, the query in the plan is not executed. This means that:
The text of the query is not parsed, so the query is not checked for valid SQL syntax.
The plans are not checked for valid abstract plan syntax.
The plans are not checked to determine whether they are compatible with the SQL text.
To guard against errors and problems, you should immediately execute the specified query with showplan enabled.
