Search

Wednesday, June 4, 2014
0 comments

Types of Secondary XML Indexes in SQL Server

7:38 PMWednesday, June 4, 2014
These are non-clustered index of the primary XML index. There must be a primary xml index before each secondary xml index. Following are some types of secondary xml indexes:

Path Indexes

The path index is built on the path value columns of the primary XML indexes. This index improves the performance of queries that use paths and values to select data.

For example, if you execute a query that checks for the existence of a product model ID using an XQuery expression as /PD:ProductDescription/@ProductModelID[.=”19”], you can create a path secondary index on the CatalogDescription column of the ProductModel table. In this path index, you can use the primary index created previously.

The following statement creates a Path index on the CatalogDescription column:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PATH ON Production.ProductModel (CatalogDesctiption)USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH

The preceding code create a path index, Pldx_ProductModel_CatalogDesctiption_PATH

Value Indexes

The value indexes contain the same items as path indexes but in the reverse order. It contains the value of the column first and then the path id. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that checks the existence of a node in an XQuery expression such as//Item@ProductID=”1”], you can create a value secondary index by using the primary index created previously.

The following statement creates a value index on the CatalogDesctiption column:

CREATE XML INDEX PIdx_ProductModel_CatalogDesctiption_VALUE ON Production.ProductModel (CatalogDesctiption)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

The preceding code creates a value index,
PIdx_ProductModel_CatalogDescription_VALUE on the CatalogDescription column of the table.

Property Indexes

The property index contains the primary key of the base table, path id, and the clause columns of primary XML indexes. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that returns a value of the node in an XQuery expression, such as /ItemList/Item/@ProductID)[1], you can create a property secondary index on the CatalogDescription column of the ProductModel table by using the following statement:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PROPERTY ON Production.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PROPERTY

The preceding code creates a property index, PIdx_ProductModel_CatalogDescription_PROPERTY, on the CatalogDescription column of the table.

You need to consider the following guidelines while creating an XML index:

  • XML indexes can be created only on XML columns.
  • XML indexes only support indexing a single XML column.
  • XML indexes can only be added to tables, views, table-valued variables with XML column or XML variables.
  • XML indexes created on a table do not allow you to modify the primary key. To do so, you first need to drop all the XML indexes on the table.
Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top