Can an index on one table be used in the query for another table?

Question

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?

Answer 1

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.

Answer 2

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