lundi 1 décembre 2014

SQL Server 2008R2 XML Column Index strategy for large tables?


I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking new to the idea of indexing XML columns.


The Goals


I have two separate scenarios in which I need to query XML:



  1. In one case, I need to query if a nested value exists across a table with 3 million rows

    • E.g. columnName.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1'



  2. In another case, I need to extract two values from XML into two columns so that I can match on it, in a table with 4.7 million rows.

    • E.g. SELECT columnName.value('Parent[1]/FilePath[1]', 'nvarchar(max)') as FilePath, columnName.value('Parent[1]/FileName[1]', 'nvarchar(255)') as FileName




Question



  • Given the cost of the indexes here and the fixed type of querying, what are indexes I can apply for maximum impact, and are there any pitfalls I should be aware of?





Aucun commentaire:

Enregistrer un commentaire