Selective XML Index – Secondary Selective XML Indices

I've only been talking so far about the "primary" Selective XML Index. But you can also create 0-n "secondary" Selective XML Indexes. The syntax looks a little bit like secondary "non-selective" XML Indexes in that you use the "USING [related XML index]". You specify one (and only one) pathspec. Additionally, the XML value that the pathspec "points to" (see previous post) cannot be data type xs:untypedAtomic (varbinary(max)).

create xml index sxi_secondary1 on foo(thexml)
using xml index fooidx
for (pathname1); — strongly typed to SQLVARCHAR(25)

create xml index sxi_secondary2 on foo(thexml)
using xml index fooidx
for (anotherxq); — strongly typed to XQUERY xs:double (SQL FLOAT)

— Msg 102, Level 15, State 1, Line 3
— Incorrect syntax near ','.
— Means: error: only one pathspec name allowed
create xml index sxi_secondary3 on foo(thexml)
using xml index fooidx
for (pathname1, pathname2);

— Msg 6391, Level 16, State 0, Line 1
— Path 'pathname2' is promoted to a type that is invalid for use as a key column in a secondary selective XML index.

— Means: can't have varbinary(max) as index key
create xml index sxi_secondary3 on foo(thexml)
using xml index fooidx
for (pathname2);

These work the way you'd think. They create a NONCLUSTERED index over the (internal) table that comprises the "primary" Selective XML Index. The NONCLUSTERED index key is the "value" column in the path spec, along with the traditional "backpointer" to the primary key of the "primary" Selective XML index. These NONCLUSTERED index are FILTERED indexes, filtered on the value column in the path spec.The filter is "[Valuecolumn] IS NOT NULL"

So, if an XML value in a pathspec is sparse (meaning, it doesn't appear in every/most rows) these secondary Selective XML Indexes could be helpful. And remember, like Michael said, these indexes are chosen by the optimizer, not the algebrizer, until the way the algebrizer chooses the primary Selective XML index, but after the algebrizer decides that the Selective XML Index could be useful at all.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.