Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Making Sense of the XML DataType in SQL Server 2005 : Page 4

In SQL Server 2005, XML becomes a first-class data type. Developers can make minor remote modifications to stored XML documents easily, taking advantage of new support for XML schema-based strong typing, and server-based XML data validation.


advertisement
XML Indexes
As you might expect, the speed of searches based on XML data in the database varies depending on how indexes are set up. For XML data, there are special indexes called XML Indexes. These indexes have a subset of the full configurability that standard indexes have, but they treat XML in a way that aids in the speed of performing searches though XML data.

Requirements
There are some limits regarding indexes on XML columns:

  • The only indexes that can be created for XML columns are XML indexes.
  • You can only add XML indexes to tables, views, table-valued variables with XML columns, or XML variables.
  • An XML index only supports indexing a single XML column.
  • Once XML indexes exist on a table, you cannot modify the primary key. If you need to do so, you must drop all XML indexes first.
Index Types
If your table meets the requirements, you can create indexes on the XML data in your tables.

The first of these index types is the primary XML index. Like its name suggests, there can be only one primary XML index on any table. You can use the CREATE PRIMARY XML INDEX command to create the primary XML index:

CREATE PRIMARY XML INDEX IXML_Teams ON Team (TeamDoc)

The primary XML index on a column creates a lookup based on the nodes of each node of the XML. Although this allows for speedy retrieval of individual nodes, there are other types of queries that benefit from their own indexes.

SQL Server 2005 supports three types of secondary indexes; PATH, PROPERTY, and VALUE. These secondary indexes are used to optimize certain types of operations as necessary. These secondary indexes are based on the Primary index and are used to tune specific types of queries. The secondary index types are listed in Table 2.

Table 1: XML Index types.
Index Type Description
PATH Builds an index on the path and value columns of the primary index to make path-specific exist() method calls more efficient (e.g., TeamDoc.exist('/ /Pitcher[@name = "John Smoltz"]'))
PROPERTY Builds an index on the PK, path and value columns of the primary index to make value() method calls more efficient (e.g., TeamDoc.value('(/Team/@name)[1]'))
VALUE Builds an index on the value and path of the primary index to make node-based exist() method calls more efficient (e.g., TeamDoc.exist('//Pitcher[@role="Closer"]'))

Use the CREATE XML INDEX syntax to create a secondary index. After you specify the table and column name, add the USING XML INDEX clause.

CREATE XML INDEX IXML_Team_Path ON Team (TeamDoc) USING XML INDEX IXML_Teams FOR PATH

The USING XML INDEX clause takes the name of the primary XML index to create the secondary index and a type (PATH, PROPERTY or VALUE) of index. For example, create the PROPERTY or VALUE secondary indexes:



CREATE XML INDEX IXML_Team_Prop ON Team (TeamDoc) USING XML INDEX IXML_Teams FOR PROPERTY CREATE XML INDEX IXML_Team_Value ON Team (TeamDoc) USING XML INDEX IXML_Teams FOR VALUE

Index Maintenance
Maintaining and modifying XML indexes is similar to maintaining standard indexes. You use the ALTER INDEX and DROP INDEX syntax for modifying XML indexes. These commands are the same commands you use for standard indexes:

ALTER INDEX IXML_Teams ON Teams REBUILD DROP INDEX IXML_Teams ON Teams



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date