SQL Server: XML Indexes

XML indexes can be used to optimize queries on an xml column. The optimizer uses these indexes differently than regular indexes, but they can often improve performance based on the given workload. XML indexes can be either primary or secondary. A primary index must be created first. Each xml column in a table can have only one primary XML index defined. Primary indexes can be created on xml columns containing either typed or untyped XML. The primary XML index uses all paths and values in the xml column to create an index with a B-Tree structure. DBA/Developer create a primary XML index using the CREATE PRIMARY XML INDEX statement. In this scenario, DBA/Developer could create a primary XML index on the TransItems column using the following statement:

CREATE PRIMARY XML INDEX PXML_TransItems

ON Transactions(TransItems);

DBA/Developer can also use secondary XML indexes to improve performance of specific queries. A secondary XML index can be created on an xml column only after a primary XML index has been created. When creating a secondary index, DBA/Developer omit the PRIMARY keyword and specify the primary XML index in the USING XML INDEX clause. Secondary XML indexes can be one of three types: PATH, VALUE, or PROPERTY. The type of secondary index is specified by the FOR clause. VALUE secondary XML indexes can improve performance if queries are often performed searching for specific values without specified paths. PATH secondary XML indexes may improve performance when path expressions are often used in queries. PROPERTY secondary XML indexes can improve performance for queries that use the value() method to retrieve one or more values. For example, in this scenario, DBA/Developer might create a secondary XML index using the following statement:

CREATE XML INDEX SXML_TransItems

ON Transactions(TransItems)
USING XML
INDEX PXML_TransItems
FOR VALUE;

DBA/Developer should not add code to the stored procedure to shred the data found in the TransItems column into columns in another table and create a composite index on the new table. In this scenario, the TransItems column is used by other applications, so it is best to leave it in XML format rather than shredding it into relational data. In addition, this solution would likely decrease performance of the GetItems stored procedure. DBA/Developer should not create a VALUE secondary index or a PATH secondary index on the TransItems column because to create a secondary XML index, a primary XML index must first be created. DBA/Developer must specify the previously created primary XML index in the USING XML INDEX clause. If DBA/Developer attempt to create a secondary index omitting the USING XML INDEX clause, the statement will generate a syntax error.