CrazyXMLIndexTest

create table dbo.T
(
  ID int identity primary key,
  X xml not null
);

go

-- Add some XML stuff
insert into dbo.T(X)
select X.X
from sys.all_columns as C
  cross apply (select C.* for xml path('columns'), type) as X(X);

go

-- Primary XML index and a secondary Path index
create primary xml index IX_T on dbo.T(X);
create xml index IX_T_P on dbo.T(X) using xml index IX_T for path;

go

-- Make sure that a node exists, anywhere
insert into dbo.T(X) values('<NodeThatExist/>');

go

-- Search for a node that exist
-- Uses secondary xml index, Good
select count(*)
from dbo.T
where T.X.exist('NodeThatExist') = 1;

-- Search for node that does not exist, anywhere
-- Table scan on primary xml index, Bad
select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1;

-- Crazy workaround, don't actually insert the node
begin transaction;
insert into dbo.T(X) values('<NodeDoesNotExist/>');
rollback;

-- Uses a seek plan
select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1;

-- Restart SQL Server or actual server does not matter
-- Still uses a seek plan
select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1;