XML Index
An
XML index is designed to handle indexing XML type column and has been available
since SQL Server 2005. Many organizations store and transmit their data in XML
format. We know XML is very convenient and popular to consume and provide the
data; that is the reason the SQL server team worked on this and increased the
performance of XML query using XML Indexing. When these indexes are used
properly, they can dramatically reduce execution time in queries made against
XML columns.
Why XML Index
When
An index does not exist, an XML field must be 'shred' when the query is
executed. This means that XML data is separated from XML tags, and is arranged
in a relational format. An XML index works before time, represents XML data in
the already-shredded version, allowing easy filtering.
You must know some XML Index Ground
Rules
The
XML index can only be applied to XML data type columns. To use the XML index on
a specific table, there should be a clustered index on the primary key column in
the table
Note: a primary key constraint includes a clustered
index upon creation by default.
There
are two types of XML indexes, first is Primary, and second is Secondary.
A
THE Primary XML index essentially
contains one row of information for each node in the XML column. This
information is made up of XML node values, types, and paths. A primary index is
a ‘pre-shredded’ representation of the XML blob – an easy-to-filter copy of the
XML data.
Secondary XML indexes are dependent on primary XML indexes – you
cannot create any secondary indexes without first having created a primary
index.
Secondary
XML indexes come in 3 types: PATH, VALUE, and PROPERTY indexes. Secondary
indexes are designed to provide additional optimization for certain types of
XML queries.
1- PATH
Secondary indexes often help to optimize queries that make use of a path. An
exist() method in the query usually indicates that a PATH index may improve the
query execution.
2-
PROPERTY
secondary indexes are designed to be helpful with queries where the primary key
the field is used, and where multiple values are retrieved from a single XML
instance.
3- VALUE
secondary indexes are good for queries that use wildcards in a path to find
known values – if the full path is not known, but the value being filtered IS
known, a VALUE index will probably optimize query execution.
How to create XML Index
As
we know the primary key column and clustered index is mandatory to create an XML
index so we have to create clustered index first. Using the following index we can create the XML index.
CREATE TABLE
Product.Inventory (ID INT PRIMARY KEY, XmlDetails XML);
GO
-- Create primary index.
CREATE PRIMARY
XML INDEX PIdx_Inventory_XmlDetails
ON T(XmlCol);
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_Inventory_XmlDetails_PATH
ON T(XmlDetails)
USING XML INDEX PIdx_Inventory_XmlDetails
FOR PATH;
GO
CREATE XML INDEX PIdx_T_XmlDetails_VALUE
ON T(XmlDetails)
USING XML INDEX PIdx_Inventory_XmlDetails
FOR VALUE;
GO
CREATE XML INDEX PIdx_Inventory_XmlDetails_PROPERTY
ON T(XmlDetails)
USING XML INDEX PIdx_Inventory_XmlDetails
FOR PROPERTY;
GO
|
In
above example, suppose we have a table Inventory which contains all information
if your product and XML columns which have all information like Product details,
SKU, ISBN, etc. in XML format if we want to increase the XML query performance
then we need indexing of this column using the above script.
