當 where 子句過濾 value() 時，為什麼不使用二級選擇性索引？

設置：

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;

每行的範例 XML：

<Number>314</Number>

查詢的任務是計算T指定值為 的行數<Number>。

有兩種明顯的方法可以做到這一點：

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

事實證明，value()選擇性exists()XML 索引需要兩個不同的路徑定義才能工作。

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版本是為value()，版本xquery是為exist()。

您可能認為這樣的索引會給您一個很好的查找計劃，但選擇性 XML 索引是作為系統表實現的，其中主鍵是T系統表的聚集鍵的前導鍵。指定的路徑是該表中的稀疏列。如果您想要一個已定義路徑的實際值的索引，您需要創建一個二級選擇性索引，每個路徑表達式一個。

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);

的查詢計劃exist()在輔助 XML 索引中進行搜尋，然後在系統表中為選擇性 XML 索引進行鍵查找（不知道為什麼需要這樣做），最後它會進行查找T以確保確實存在行在那裡。最後一部分是必要的，因為系統表和T.

查詢的計劃value()不是很好。它T使用嵌套循環連接對內部表上的搜尋進行聚集索引掃描，以從稀疏列中獲取值，最後過濾該值。

是否應該使用選擇性索引是在優化之前決定的，但是是否應該使用二級選擇性索引是優化器基於成本的決定。

為什麼 where 子句過濾時不使用二級選擇性索引value()？

更新：

查詢在語義上是不同的。如果添加具有值的行

<Number>313</Number>
<Number>314</Number>` 

該exist()版本將計為 2 行，而values()查詢將計為 1 行。但是使用singletonSQL Server 指令在此處指定的索引定義將阻止您添加具有多個<Number>元素的行。

然而，這並不允許我們在values()不指定[1]保證編譯器我們只會得到一個值的情況下使用該函式。這[1]就是我們在value()計劃中有 Top N Sort 的原因。

看起來我正在接近這裡的答案……

索引路徑表達式中的聲明singleton強制您不能添加多個元素，但 XQuery 編譯器在解釋函式<Number>中的表達式時不會考慮到這一點。value()您必須指定[1]使 SQL Server 滿意。使用帶有模式的類型化 XML 也無濟於事。正因為如此，SQL Server 建構了一個查詢，該查詢使用了可以稱為“應用”模式的東西。

最容易展示的是使用正常表而不是 XML 來模擬我們實際執行的查詢T和內部表。

這是將內部表設置為真實表的設置。

create table dbo.xml_sxi_table
(
 pk1 int not null,
 row_id int,
 path_1_id varbinary(900),
 pathSQL_1_sql_value int,
 pathXQUERY_2_value float
);

go

create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;

go

insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;

有了這兩個表，您就可以執行等效的exist()查詢。

select count(*)
from dbo.T
where exists (
            select *
            from dbo.xml_sxi_table as S
            where S.pk1 = T.ID and
                  S.pathXQUERY_2_value = 314 and
                  S.path_1_id is not null
            );

查詢的等效項value()如下所示。

select count(*)
from dbo.T
where (
     select top(1) S.pathSQL_1_sql_value
     from dbo.xml_sxi_table as S
     where S.pk1 = T.ID and
           S.path_1_id is not null
     order by S.path_1_id
     ) = 314;

top(1)andorder by S.path_1_id是罪魁禍首，應該歸咎於[1]Xpath 表達式。

我認為微軟不可能用內部表的目前結構來解決這個問題，即使你被允許[1]從values()函式中省略。他們可能必須為每個路徑表達式創建多個內部表，並設置獨特的約束，以向優化器保證每一行只能有一個<number>元素。不確定這實際上是否足以讓優化器“突破應用模式”。

對於那些認為這很有趣，並且因為你還在閱讀這篇文章的你，你可能是。

一些查詢來查看內部表的結構。

select T.name, 
      T.internal_type_desc, 
      object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');

select C.name as column_name, 
      C.column_id,
      T.name as type_name,
      C.max_length,
      C.is_sparse,
      C.is_nullable
from sys.columns as C
 inner join sys.types as T
   on C.user_type_id = T.user_type_id
where C.object_id in (
                    select T.object_id 
                    from sys.internal_tables as T 
                    where T.parent_id = object_id('T')
                    )
order by C.column_id;

select I.name as index_name,
      I.type_desc,
      I.is_unique,
      I.filter_definition,
      IC.key_ordinal,
      C.name as column_name, 
      C.column_id,
      T.name as type_name,
      C.max_length,
      I.is_unique,
      I.is_unique_constraint
from sys.indexes as I
 inner join sys.index_columns as IC
   on I.object_id = IC.object_id and
      I.index_id = IC.index_id
 inner join sys.columns as C
   on IC.column_id = C.column_id and
      IC.object_id = C.object_id
 inner join sys.types as T
   on C.user_type_id = T.user_type_id
where I.object_id in (
                    select T.object_id 
                    from sys.internal_tables as T 
                    where T.parent_id = object_id('T')
                    );

引用自：https://dba.stackexchange.com/questions/103867