SQL Server Performance

XML Indexing

Discussion in 'SQL Server 2005 General Developer Questions' started by Siona, Mar 3, 2009.

  1. Siona New Member

    Dear Friends,
    I need to know what XML Index is and what architecture it’s following.
    And how it’s working during the execution..??
  2. Sandy New Member

  3. Siona New Member

    Can you please make more clear on this..??
  4. Sandy New Member

    Actually SQL Server 2005 supports four different types of XML indexes. Since an XML index is somewhat different than a relational index, it is necessary to know their implementation before we approach how to use them for maximum effectiveness. There is a single "primary XML index" and three different flavors of "secondary XML index". And it turns out that the primary XML index isn't strictly an index on the original form of the XML.
    The primary XML index on an XML column is a clustered index on an internal table known as the node table that users cannot use directly from their T-SQL statements. The primary XML index is a B+tree and its usefulness is due to the way that the optimizer creates a plan for the entire query. Although the optimizer can operate on the entire XML column as though it is a blob, when you need to execute XML queries, it is more often useful to decompose the XML into relational columns and rows. The primary XML index essentially contains one row for each node in the XML instance.
    Once the primary XML index has been created, an additional three kinds of secondary XML index can be created. The secondary XML indexes assist in certain types of XQuery processing. These are called the PATH, PROPERTY, and VALUE indexes.
    Secondary XML indexes are actually indexes on the node table. The PATH index, for example in a normal non-clustered index on the (HID, VALUE) columns of the node table.
    Once the primary XML index is in place, the optimizer chooses which indexes to use. If you have all three secondary indexes, there are actually four choices:
    1. Index scan or seek on the primary XML index
      • Index scan or seek on node table's PATH index
        • Index scan or seek on node table's PROPERTY index
          • Index scan or seek on node table's VALUE index
        • The primary XML index is clustered (data is stored) in XML document order; this makes it ideal for processing subtrees. Much of the work in XML queries consists of processing subtrees or assembling an answer by using subtrees, so the clustered index on the node table will be frequently used. Here's an example of how the same query plan looks after only the primary XML index is created.
          I think Seeona this helps you. and please go through properly to make you more clear.
  5. Siona New Member

    Hey Sandy,[:)]
    Nice Explanation...
    Heartily Thanks...

Share This Page