create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( select top(10000) row_number() over(order by (select null)) as Number from sys.columns as c1, sys.columns as c2 ) as N;
Sample XML for each row:
The job for the query is to count the number of rows in
T with a specified value of
There are two obvious ways to do this:
select count(*) from dbo.T as T where T.XMLDoc.value('/Number', 'int') = 314; select count(*) from dbo.T as T where T.XMLDoc.exist('/Number[. eq 314]') = 1;
It turns out that
exists() requires two different path definitions for the selective XML index to work.
create selective xml index SIX_T on dbo.T(XMLDoc) for ( pathSQL = '/Number' as sql int singleton, pathXQUERY = '/Number' as xquery 'xs:double' singleton );
sql version is for
value() and the
xquery version is for
You might think that an index like that would give you a plan with a nice seek but selective XML indexes are implemented as a system table with the primary key of
T as the lead key of the clustered key of the system table. The paths specified are sparse columns in that table. If you want an index of the actual values of the defined paths you need to create a secondary selective indexes, one for each path expression.
create xml index SIX_T_pathSQL on dbo.T(XMLDoc) using xml index SIX_T for (pathSQL); create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc) using xml index SIX_T for (pathXQUERY);
The query plan for the
exist() does a seek in the secondary XML index followed by a key lookup in the system table for the selective XML index (don't know why that is needed) and finaly it does a lookup in
T to make sure there actually are rows in there. The last part is necessary because there is no foreign key constraint between the system table and
The plan for the
value() query is not so nice. It does a clustered index scan of
T with a nested loops join against a seek on the internal table to get the value from the sparse column and finally filters on the value.
If a selective index should be used or not is decided before optimization but if a secondary selective index should be used or not is a cost based decision by the optimizer.
Why is the secondary selective index not used when the where clause filters on
The queries are semantically different. If you add a row with the value
exist() version would count 2 rows and the
values() query would count 1 row. But with the index definitions as they are specified here using the
singleton directive SQL Server will prevent you from adding a row with multiple
That does however not let us use the
values() function without specifying
 to guarantee the compiler that we will only get a single value. That
 is the reason we have a Top N Sort in the
Looks like I am closing in on an answer here...