December 21, 2014
Hot Topics:

XML Queries and Indexing in SQL Server 2005

  • November 22, 2005
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

Primary XML Indexes

XML columns are stored as binary large objects (BLOBs) in your database. Because of this, queries that search within an XML column can be slow. You can speed up these queries by creating a primary XML index on each XML column. A primary XML index is a special index that "shreds" the XML data to store information including:

  • Each tag name in the XML
  • The path from the root of the document to the tag
  • The value of the node
  • The type of the node
  • The corresponding primary key of the base table

To create a primary XML index, execute the CREATE PRIMARY XML INDEX statement:


CREATE PRIMARY XML INDEX index_name
    ON table_name (xml_column_name)

For the table I've been working with, this would be:


CREATE PRIMARY XML INDEX PXML_Document
    ON Document (DocumentStore)

A primary XML index will speed up most queries against an XML column, and it's a good idea to create one whenever you'll be searching XML columns that contain large amounts of data.

Secondary XML Indexes

In addition to the primary XML index, each XML column can have up to three secondary XML indexes. These are specialized XML indexes that help with particular types of XML queries, and can only be created on columns that already have a primary XML index:

  • The PATH secondary XML index helps with queries that use XML path expressions.
  • The VALUE secondary XML index helps with queries that search for values anywhere in the XML document.
  • The PROPERTY secondary XML index helps with queries that retrieve particular object properties from within an XML document.

To create a secondary XML index, you must use the CREATE XML INDEX statement:

CREATE XML INDEX index_name 
    ON table_name (xml_column_name)
    [USING XML INDEX xml_index_name 
        [FOR {VALUE|PATH|PROPERTY}]

Maintaining XML indexes is costly, especially if the data in the XML column changes frequently. Avoid creating secondary XML indexes unless you frequently perform the types of queries that they are specifically designed to help with.

Use Your Power Wisely

A final word of caution: just because SQL Server 2005 can store XML columns doesn't mean that you have to put XML columns in every database. In many cases, it will make more sense to break down the data that might go into XML columns and make regular relational columns from it instead. But in cases where SQL Server needs to interoperate with some other component that already depends on XML, storing data in XML columns can make a lot of sense. That way you can avoid doing constant data conversions, and use a pure XML format throughout the business process. In those cases, think carefully about your querying requirements, create indexes as necessary, and test performance carefully. You'll prohably find that XML columns make your life considerably easier.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.





Page 2 of 2



Comment and Contribute

 


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

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel