Share via

  • Article

Indexes on XML Data Type Columns

XML indexes can be created on xml data type columns. They index all tags, values and paths over the XML instances in the column and benefit query performance. Your application may benefit from an XML index in the following situations:

  • Queries on XML columns are common in your workload. XML index maintenance cost during data modification must be considered.

  • Your XML values are relatively large and the retrieved parts are relatively small. Building the index avoids parsing the whole data at run time and benefits index lookups for efficient query processing.

XML indexes fall into the following categories:

  • Primary XML index

  • Secondary XML index

The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Depending on the type of queries, these secondary indexes might help improve query performance.

Note

You cannot create or modify an XML index unless the database options are set correctly for working with the xml data type. For more information, see Full-Text Index on an XML Column.

XML instances are stored in xml type columns as large binary objects (BLOBs). These XML instances can be large, and the stored binary representation of xml data type instances can be up to 2 GB. Without an index, these binary large objects are shredded at run time to evaluate a query. This shredding can be time-consuming. For example, consider the following query:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

To select the XML instances that satisfy the condition in the WHERE clause, the XML binary large object (BLOB) in each row of table Production.ProductModel is shredded at run time. Then, the expression (/PD:ProductDescription/@ProductModelID[.="19"]) in the exist() method is evaluated. This run-time shredding can be costly, depending on the size and number of instances stored in the column.

If querying XML binary large objects (BLOBs) is common in your application environment, it helps to index the xml type columns. However, there is a cost associated with maintaining the index during data modification.

In This Section

Topic

Description

Primary XML Index

Describes the primary XML index and its rows.

Secondary XML Indexes

Describes the three types of secondary XML indexes.

Creating XML Indexes

Describes how to create primary and secondary indexes.

Modifying XML Indexes

Describes how to use the ALTER INDEX statement to modify XML indexes.

Dropping XML Indexes

Describes how to use the DROP INDEX statement to drop indexes.

Full-Text Index on an XML Column

Describes how to create a full-text index on XML columns.