Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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
