XML Indexes

The XML data type supports a maximum of 2GB of storage, which is quite large. The size of the XML data and its usage can have a big impact on the performance the system can achieve while querying the XML data. To improve the performance of XML queries, SQL Server 2005 provides the ability to create indexes over the columns that have the XML data type.

Primary XML Indexes

In order to create an XML index on an XML data type column, a clustered primary key must exist for the table. In addition, if you need to change the primary key for the table you must first delete the XML index. An XML index covers all the elements in the XML column, and you can have only one XML index per column. Because XML indexes use the same namespace as regular SQL Server relational indexes, XML indexes cannot have the same name as an existing index. XML indexes can be created only on XML data types in a table. They cannot be created on columns in views or on XML data type variables. A primary XML index consists of a persistent shredded representation of the data in the XML column. The code to create a primary XML index is shown in the following listing:

CREATE PRIMARY XML INDEX MyXMLDocsIdx ON MyXMLDocs(MyXMLDoc)

This example shows the creation of a primary XML index named MyXMLDocsIdx. This index is created on the MyXMLDoc XML data type column in the MyXMLDocs table. Just like regular SQL Server indexes, XML indexes can be viewed by querying the sys.indexes view.

SELECT * FROM sys.indexes WHERE name = 'MyXMLDocsIdx'

Secondary XML Indexes

In addition to the primary index, you can also build secondary XML indexes. Secondary indexes are built on one of the following document attributes:

• Path The document path is used to build the index.

• Value The document values are used to built the index

• Property The documents properties are used to build the index

Secondary indexes are always partitioned in the same way as the primary XML index. The following listing shows the creation of a secondary-path XML index:

CREATE XML INDEX My2ndXMLDocsIdx ON MyXMLDocs(MyXMLDoc)  USING XML INDEX MyXMLDocsIdx FOR PATH