The question is, how can i make counting by xml as fast as counting by
Depending on how flexible you need to be with your xPath expression you could use property promotion.
create function dbo.GetSomeText(@X xml) returns varchar(8) with schemabinding as
return @X.value('(/SomeText/text())', 'varchar(8)');
alter table dbo.T ...
With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index:
and filter on moretext with a seek predicate:
An interesting part here is that it executes the key lookup to get the path_1_id values that are not null.
Since that is a filter definition on the nonclustered xml index...
...while not being ...