Edit

CREATE XML INDEX (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Creates an XML index on a specified table. An index can be created before there is data in the table. XML indexes can be created on tables in another database by specifying a qualified database name.

Note

To create a relational index, see CREATE INDEX (Transact-SQL). For information about how to create a spatial index, see CREATE SPATIAL INDEX (Transact-SQL).

Transact-SQL syntax conventions

Syntax

--Create XML Index   
CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ database_name.schema_name.table_name | schema_name.table_name | table_name }

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | XML_COMPRESSION = { ON | OFF }
}

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

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 can't create an XML index on a non-xml column, nor can you create a relational index on an xml column.

  • You can't 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 can't 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 XML Indexes (SQL Server).

index_name

The name of the index. Index names must be unique within a table but don't have to be unique within a database. Index names must follow the rules of identifiers.

Primary XML index names can't start with the following characters: #, ##, @, or @@.

xml_column_name

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.

FOR { VALUE | PATH | PROPERTY }

Specifies the type of 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>::=

The fully qualified or nonfully qualified object to be indexed.

database_name
The name of the database.

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

table_name
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 isn't 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 isn't 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 doesn't 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.

For more information, see Specify Fill Factor for an 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 SORT_IN_TEMPDB Option For Indexes.

IGNORE_DUP_KEY = OFF

Has no effect for XML indexes because the index type is never unique. Don't 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 can't be changed by using DROP_EXISTING. Also, a primary XML index can't be redefined as a secondary XML index, or vice versa.

ONLINE = OFF

Specifies that underlying tables and associated indexes aren't available for queries and data modification during the index operation. In this version of SQL Server, online index builds aren't supported for XML indexes. If this option is set to ON for an 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 an XML index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table during the operation.

Note

Online index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022.

ALLOW_ROW_LOCKS = { ON | OFF }

Specifies whether row locks are allowed. The default is ON.

ON
Row locks are allowed when accessing the index. The Database Engine determines when row locks are used.

OFF
Row locks aren't used.

ALLOW_PAGE_LOCKS = { ON | OFF }

Specifies whether page locks are allowed. The default is ON.

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

OFF
Page locks aren't used.

MAXDOP = max_degree_of_parallelism

Overrides the Configure the max degree of parallelism Server Configuration Option configuration option during 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.

For more information, see Configure Parallel Index Operations.

Note

Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022.

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 can't create a primary XML index on a computed xml column.

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

For more information about XML indexes, see XML Indexes (SQL Server).

XML compression

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

  • XML indexes don't inherit the compression property of the table. To compress indexes, you must explicitly enable XML compression on XML indexes.
  • Secondary XML indexes don't inherit the compression property of the Primary XML index.
  • By default, the XML compression setting for XML indexes is set to OFF when the index is created.

Additional remarks on index creation

For more information about index creation, see the "Remarks" section in CREATE INDEX (Transact-SQL).

Examples

This article requires the AdventureWorks2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Creating a primary XML index

The following example creates a primary XML index on the CatalogDescription column in the Production.ProductModel table.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);  
GO

B. Creating a primary XML index with XML compression

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

The following example creates a primary XML index on the CatalogDescription column in the Production.ProductModel table.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription)
    WITH (XML_COMPRESSION = ON);  
GO

C. Creating a secondary XML index

The following example creates a secondary XML index on the CatalogDescription column in the Production.ProductModel table.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

D. Creating a secondary XML index with XML compression

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

The following example creates a secondary XML index on the CatalogDescription column in the Production.ProductModel table.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH
    WITH (XML_COMPRESSION = ON);
GO

See also