asked
How to make use of xml indexing?
I have a table containing a column of data type xml. I have to create index on this column, since I have to join and also has queries with search criteria. If I have to add index on this how well it can perform? Also, the number of nodes and their types are not consistent. can you please help me to resolve this issue to get better performance. Regards, --Rudra
You can't simply index XML. Instead, you need to use the specific XML indexes, [outlined here][1]. But, these are for very specific scenarios where you are retrieving known structures, and small subsets of those known structures. You're saying that you have inconsistent XML data types and that you're using this information for JOINs. That is absolutely not the scenario where an XML index is going to help. In fact, you're using a structure that is extremely unlikely to perform well. XML is not a substitute for proper normalized data structures. Using XML to JOIN with other tables is invariably going to perform slowly. SQL Server can parse XML and does so in a relatively timely manner. But, it's not the primary mechanisms for data retrieval and storage within SQL Server. As such, it's not optimized the way standard structured relational storage is. [1]:
http://msdn.microsoft.com/en-us/library/ms191497.aspx
Hi Grant, Thanks for your comment. Let me tell you the actual requirement for which I have decided to have xml column type. Our customers wants to store multiple custom field/values in UI. I can not have that many number of columns in the table. If I keep adding the columns based on the user defined custom fields, it will end up having lots of columns. So I decided to store the data in xml form and store it in a single column. The fields will vary for each customer. Here is an example:- customer 1: '27/07/2014' 20 'online' customer 2: 'Mumbai' '01/08/2014' 4
OK, that's a choice. But you're still going to suffer attempting to use that data in queries to attempt to JOIN it to other tables. There isn't an efficient way to do that. It's not how SQL Server is designed. It's not how XML indexes work. Go and read the link I provided to get an idea how they work. If you have relational data, I strongly recommend you store it in a relational fashion using appropriate data types. Instead of just adding columns to a table (another poor design choice anyway), use the relational engine to create a table of additional look up values, possibly a many-to-many relationship to store that data. You can't have it both ways. You can't have unstructured, anything goes storage, and the behavior of a structured storage engine.
15 People are following this question.
