How to index XML fields in SQL Server 2008

from:http://blog.csdn.net/tjvictor/article/details/4370771

XML indexes in SQL Server fall into two categories: primary XML indexes and secondary XML indexes. The secondary XML index is divided into: PATH secondary XML index, VALUE Secondary XML index, property auxiliary XML index.

An example of the syntax for creating an XML index is as follows:

[C-sharp]View PlainCopy

2. CREATE TABLE XMLTable (Id int primary key, xmlcol XML);
4. Go
6. --xml Primary Index
8. Create primary XML index Ipxml_xmltable_xmlcol on XMLTable (Xmlcol);
10. --xml Path Secondary Index
12. Create XML index Ixml_xmltable_xmlcol_path on XMLTable (Xmlcol)
14. Using XML index Ipxml_xmltable_xmlcol for path
16. --xml Property Secondary Index
18. Create XML index Ixml_xmltable_xmlcol_property on XMLTable (Xmlcol)
20. Using XML index Ipxml_xmltable_xmlcol for property
22. --xml Content Secondary Index
24. Create XML index Ixml_xmltable_xmlcol_value on XMLTable (Xmlcol)
26. Using XML index Ipxml_xmltable_xmlcol for value

It is important to note that the table that establishes the XML index must have a primary key.

The benefit of indexing is to improve query efficiency, and the downside is to increase storage space. The following examples illustrate:

1. First build the test table, execute the following SQL statement in SSMs CREATE TABLE XMLTable (Id int primary key, xmlcol XML);

2. The following procedure is to add 600,000 data to the table to facilitate test performance. As for why add a program instead of an INSERT statement, see my other blog: SQL Server two ways to bulk insert data http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx

[C-sharp]View PlainCopy

2. static void Main (string[] args)
4. {
6. DataTable dt = GetTableSchema ();
8. For (int count = 1; count <= 600000; count++)
10. {
12. DataRow r = dt. NewRow ();
14. R[0] = count;
16. R[1] = Getpropertyxml ();
18. Dt. Rows.Add (R);
20. }
22. BULKTODB (DT);
26. Console.WriteLine ("finished");
28. Console.ReadLine ();
30. }
34. Public static void Bulktodb (DataTable dt)
36. {
38. SqlConnection sqlconn = new SqlConnection (
40. configurationmanager.connectionstrings["ConnStr1"].  ConnectionString);
44. SqlBulkCopy SqlBulkCopy = new SqlBulkCopy (sqlconn);
46. sqlbulkcopy.bulkcopytimeout = 0;
48. sqlbulkcopy.batchsize = dt. Rows.Count;
50. Sqlbulkcopy.destinationtablename = "XMLTable";
54. Try
56. {
58. Sqlconn.open ();
60. if (dt ! = null && dt. Rows.Count! = 0)
62. {
64. Sqlbulkcopy.writetoserver (DT);
66. }
68. }
70. catch (Exception ex)
72. {
74. throw ex;
76. }
78. finally
80. {
82. Sqlconn.close ();
84. }
86. }
90. Public static DataTable GetTableSchema ()
92. {
94. DataTable dt = new DataTable ();
96. Dt. Columns.addrange (new datacolumn[]{
98. New DataColumn ("Id",typeof (int)),
100. New DataColumn ("Xmlcol",typeof (string))});
102. return DT;
104. }
108. Public static int getrandrange (int start, int end)
110. {
112. Random random = new Random (Guid.NewGuid ().  GetHashCode ());
114. return random.  Next (start, end);
116. }
120. Public static string Getpropertyxml ()
122. {
124. StringBuilder buffer = new StringBuilder ();
126. Buffer.  Appendline ("<TJVICTOR>");
130. For (int count = 0; count < Getrandrange (1, ten); count++)
132. {
134. int basenum = getrandrange (1, 100);
136. Buffer. Appendline (string.  Format ("<item{0} v=/" property{0}/">Value{0}</Item{0}>", Basenum));
138. }
142. Buffer.  Appendline ("</TJVICTOR>");
144. return buffer.  ToString ();
146. }

3. Execute a query statement, paying attention to its execution time and execution plan:

Select Id from XMLTable
where Xmlcol.exist ('/tjvictor/item3 ') =1

Because the machine configuration is different, the execution time will not be exactly the same, here only the execution plan, for reference:

All time is spent on table valued function, and still clustered index scan.

4. Add an index to the XML field for this table.

[C-sharp]View PlainCopy

2. --xml Primary Index
4. Create primary XML index Ipxml_xmltable_xmlcol on XMLTable (Xmlcol);
6. --xml Path Secondary Index
8. Create XML index Ixml_xmltable_xmlcol_path on XMLTable (Xmlcol)
10. Using XML index Ipxml_xmltable_xmlcol for path
12. --xml Property Secondary Index
14. Create XML index Ixml_xmltable_xmlcol_property on XMLTable (Xmlcol)
16. Using XML index Ipxml_xmltable_xmlcol for property
18. --xml Content Secondary Index
20. Create XML index Ixml_xmltable_xmlcol_value on XMLTable (Xmlcol)
22. Using XML index Ipxml_xmltable_xmlcol for value

Note: Because we already have 600,000 data in our table, the index time will be very long, and will consume a lot of memory and disk, I spent 10 minutes or so, accounted for 1G of memory, and 1.3G disk. Please pay attention to your hard disk space when you build the index, or modify the previously inserted data program, less insert some data.

5. Re-execute the above SQL statement:

Select Id from XMLTable
where Xmlcol.exist ('/tjvictor/item3 ') =1

You will find that the results are instantaneous, the following is the execution plan, using the XML index seek.

Summary: After the establishment of XML index, query efficiency will greatly improve, after my test, xml.exist the most efficient execution, basically improve a data level, other statements such as Xml.query,xml.value, query speed increased by about one times, but the overall is not too ideal. But also found that the XML index too much space, such as the above 600,000 records, the space occupancy ratio is as follows:

Name rows reserved data index_size unused
XMLTable 600000 1479688 kb 160952 KB 1318184 KB 552 kb

For basic usage methods such as Xml.exist,xml.query,xml.value, please refer to the following article:

Http://blog.csdn.net/tjvictor/archive/2009/07/21/4368511.aspx

If you want to reprint, please specify the original from CSDN tjvictor column of this article:

Http://blog.csdn.net/tjvictor/archive/2009/07/22/4370771.aspx

How to index XML fields in SQL Server 2008