4

I am working in SQL Server (2014).

I have a table that contains an XML column. There are a well known set of queries against that column that I would like to materialize as persisted computed columns for querying purposes. However, I don't want to add those columns to the table for various reasons.

My question is, is there some way to create a second table that contains indexes/keys that will be used by the optimizer for the first table? Is this achievable with hints or some other mechanism?

2
  • 3
    A materialized view may accomplish your goal.
    – Hannah Vernon
    Nov 7, 2015 at 1:48
  • It would, but you can't have XML columns in a materialized view. When I attempted to use a function so that the view only had acceptable types, SQL Server still wouldn't accept it.
    – Erick T
    Nov 9, 2015 at 18:53

2 Answers 2

6

Selective XML Indexes does just that for you behind the scenes.

It allows you to specify an XPath expression in the index and when you use that expression in a query SQL Server uses a system table to retrieve the value you are looking for.

Example:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

go

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  Node1 = '/root/node1/text()' as sql int
);

go

insert into dbo.T(XMLDoc) values('
<root>
  <node1>1</node1>
</root>');

go

select T.XMLDoc.value('(/root/node1/text())[1]', 'int') 
from dbo.T;

The query plan for the XML query shows a table scan of T and a nested loops join with a seek on the primary key into the system table that holds the value you are looking for.

enter image description here

1
  • Thanks Mikael, I didn't know about these. This would remove the need for the computed column in the first place. Thanks!
    – Erick T
    Nov 9, 2015 at 18:55
1

Property Promotion for frequently used XML values

Their example shows you exactly how to use xpath within a user-defined function, which is in turn called in computed column that is persisted

2
  • Thanks Andrew, I looked into this, but I was trying to avoid adding columns (computed or otherwise) to the existing table.
    – Erick T
    Nov 9, 2015 at 18:54
  • Then Mikaels answer is your best option, giving best of for no columns but efficient in terms of query performance with just the index maintaining and space Nov 9, 2015 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.