Arguments

[PRIMARY] XML
Creates an XML index on the specified xml column. When PRIMARY is specified, a clustered index is created with the clustered key formed from the clustering key of the user table and an XML node identifier. Each table can have up to 249 XML indexes. Note the following when you create an XML index:

A clustered index must exist on the primary key of the user table.

The clustering key of the user table is limited to 15 columns.

Each xml column in a table can have one primary XML index and multiple secondary XML indexes.

A primary XML index on an xml column must exist before a secondary XML index can be created on the column.

An XML index can only be created on a single xml column. You cannot create an XML index on a non-xml column, nor can you create a relational index on an xml column.

You cannot create an XML index, either primary or secondary, on an xml column in a view, on a table-valued variable with xml columns, or xml type variables.

You cannot create a primary XML index on a computed xml column.

The SET option settings must be the same as those required for indexed views and computed column indexes. Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column. For more information, see SET Options That Affect Results.

xml_column_name
Is the xml column on which the index is based. Only one xml column can be specified in a single XML index definition; however, multiple secondary XML indexes can be created on an xml column.

USING XML INDEX xml_index_name
Specifies the primary XML index to use in creating a secondary XML index.

VALUE
Creates a secondary XML index on columns where key columns are (node value and path) of the primary XML index.

PATH
Creates a secondary XML index on columns built on path values and node values in the primary XML index. In the PATH secondary index, the path and node values are key columns that allow efficient seeks when searching for paths.

PROPERTY
Creates a secondary XML index on columns (PK, path and node value) of the primary XML index where PK is the primary key of the base table.

<object>::=

Is the fully qualified or nonfully qualified object to be indexed.

database_name
Is the name of the database.

schema_name
Is the name of the schema to which the table belongs.

table_name
Is the name of the table to be indexed.

<xml_index_option> ::=

Specifies the options to use when you create the index.

PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.

ON
The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

FILLFACTOR **=**fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity.

Note

Fill factor values 0 and 100 are the same in all respects.

The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.

Important

Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database Engine redistributes the data when it creates the clustered index.

SORT_IN_TEMPDB = { ON | OFF }
Specifies whether to store temporary sort results in tempdb. The default is OFF.

ON
The intermediate sort results that are used to build the index are stored in tempdb. This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. However, this increases the amount of disk space that is used during the index build.

OFF
The intermediate sort results are stored in the same database as the index.

In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. For more information, see tempdb and Index Creation.

IGNORE_DUP_KEY **=**OFF
Has no effect for XML indexes because the index type is never unique. Do not set this option to ON, or else an error is raised.

DROP_EXISTING = { ON | OFF }
Specifies that the named, preexisting XML index is dropped and rebuilt. The default is OFF.

ON
The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

OFF
An error is displayed if the specified index name already exists.

The index type cannot be changed by using DROP_EXISTING. Also, a primary XML index cannot be redefined as a secondary XML index, or vice versa.

ONLINE **=**OFF
Specifies that underlying tables and associated indexes are not available for queries and data modification during the index operation. In this version of SQL Server, online index builds are not supported for XML indexes. If this option is set to ON for a XML index, an error is raised. Either omit the ONLINE option or set ONLINE to OFF.

An offline index operation that creates, rebuilds, or drops a XML index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.

Note

Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

ON
Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

OFF
Page locks are not used.

MAXDOP **=**max_degree_of_parallelism
Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

Important

Although the MAXDOP option is syntactically supported for all XML indexes, for a primary XML index, CREATE XML INDEX uses only a single processor.

max_degree_of_parallelism can be:

1
Suppresses parallel plan generation.

>1
Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

0 (default)
Uses the actual number of processors or fewer based on the current system workload.

Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

Remarks

Computed columns derived from xml data types can be indexed either as a key or included nonkey column as long as the computed column data type is allowable as an index key column or nonkey column. You cannot create a primary XML index on a computed xml column.

To view information about XML indexes, use the sys.xml_indexes catalog view.