Chapter 1: Commands

create plan

Description

Creates an abstract plan.

Syntax

create plan query plan 
	[into group_name]
	[and set @new_id]

Parameters

query: is a string literal, parameter, or local variable containing the SQL text of a query.

plan: is a string literal, parameter, or local variable containing an abstract plan expression.

into group_name: specifies the name of an abstract plan group.

and set @new_id: returns the ID number of the abstract plan in the variable.

Examples

Example 1

Creates an abstract plan for the specified query:

create plan "select * from titles where price > $20" "(t_scan titles)"

Example 2

Creates an abstract plan for the query in the dev_plans group, and returns the plan ID in the variable @id:

declare @id int
create plan "select au_fname, au_lname from authors where au_id = ’724-08-9931’ " 
"(i_scan au_id_ix authors)"
into dev_plans
and set @id
select @id

Usage

create plan saves the abstract plan in the group specified with into. If no group name is specified, it saves the plan in the currently active plan group.
Queries and abstract plans specified with create plan are not checked for valid SQL syntax and plans are not checked for valid abstract plan syntax. Also, the plan is not checked for compatibility with the SQL text. You should immediately check all plans created with create plan for correctness by running the query specified in the create plan statement.
If another query plan in the group has the same SQL text, replace mode must be enabled with set plan replace on. Otherwise, the create plan command fails.
You must declare @new_id before using it in the and set clause.
The abstract plan group you specify with into must already exist.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create plan permission defaults to all users. No permission is required to use it.