xml 資料類型資料行上的索引

  • 發行項

XML 執行個體是以大型二進位物件 (BLOB) 儲存在 xml 類型資料行中。這些 XML 執行個體可以是大型的，而且所儲存之 xml 資料類型執行個體的二進位表示法最多可達 2 GB。如果沒有索引，這些二進位大型物件就會在執行階段切割，以便評估查詢。這項切割作業可能會很費時。例如，請考慮下列查詢：

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

為了選取符合 WHERE 子句中條件的 XML 執行個體，在執行階段會切割 Production.ProductModel 資料表之每個資料列中的 XML 二進位大型物件 (BLOB)。然後，便評估 exist() 方法中的運算式 (/PD:ProductDescription/@ProductModelID[.="19"])。此執行階段的切割可能會非常費時，端視資料行中所儲存的執行個體之大小與數目而定。

如果查詢 XML 二進位大型物件 (BLOB) 常在應用程式環境中發生，它將可協助索引 xml 類型的資料行。不過，在資料修改期間有維護索引的相關成本。

XML 索引可分成下列類別：

  • 主要 XML 索引
  • 次要 XML 索引

在 xml 類型資料行上的第一個索引必須是主要的 XML 索引。使用主要 XML 索引時，可支援下列次要索引類型：PATH、VALUE 及 PROPERTY。視查詢類型而定，這些次要索引可協助改善查詢效能。

主要 XML 索引

主要 XML 索引是 xml 資料類型資料行中 XML BLOB 的切割和保存的表示法。對於資料行中的每個 XML 二進位大型物件 (BLOB)，索引可建立一些資料列。在索引中的資料列數目大約等於 XML 二進位大型物件中的節點數目。

每個資料列都會儲存下列節點資訊：

  • 元素或屬性名稱等標記名稱。
  • 節點值。
  • 如元素節點、屬性節點或文字節點等節點類型。
  • 文件順序資訊，以內部節點識別碼表示。
  • 從每個節點至 XML 樹狀結構根節點的路徑。在查詢中會為路徑運算式搜尋資料行。
  • 基底資料表的主索引鍵。基底資料表的主索引鍵會在主要 XML 索引中重複，以利向後聯結基底資料表，而基底資料表主索引鍵中資料行的最大數目是限定為 15。

此節點資訊是用以評估和建構指定查詢的 XML 結果。為了達到最佳化，標記名稱與節點類型資訊將會編碼成整數值，而 Path 資料行則會使用相同的編碼。另外，當只知道路徑後置詞時，會以相反順序儲存路徑以允許比對路徑。例如：

  • 在 //ContactRecord/PhoneNumber 中只知道最後兩個步驟

OR

  • 在 /Book/*/Title 中，於運算式的中間指定了萬用字元 (*)。

查詢處理器會使用主要 XML 索引來進行包含 xml 資料類型方法的查詢，並從主要索引本身傳回純量值或 XML 子樹(這個索引會儲存重新建構 XML 執行個體的所有必要資訊)。

例如，下列查詢會傳回儲存在 ProductModel 資料表之 CatalogDescriptionxml 類型資料行中的摘要資訊。此查詢只會針對目錄描述也儲存 <Features> 描述的產品型號傳回其 <Summary> 資訊。

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

至於主要 XML 索引，而不是切割基底資料表中每個 XML 二進位大型物件的執行個體，會針對 exist() 方法中所指定的運算式，循序搜尋索引中與每個 XML 二進位大型物件相對應的資料列。如果在索引中的 Path 資料行找到了路徑，就會從主要 XML 索引擷取 <Summary> 元素及其子樹，並轉換為 XML 二進位大型物件，做為 query() 方法的結果。

請注意，在擷取完整 XML 執行個體時不會使用主要 XML 索引。例如，下列查詢會從資料表擷取整個 XML 執行個體，該執行個體描述了特定產品型號的製造指示。

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

次要 XML 索引

若要增強搜尋效能，您可以建立次要的 XML 索引。在建立次要索引前必須先有主 XML索引。以下為其類型：

  • PATH 次要 XML 索引
  • VALUE 次要 XML 索引
  • PROPERTY 次要 XML 索引

PATH 次要 XML 索引

如果您的查詢通常會在 xml 類型資料行上指定路徑運算式，則使用 PATH 次要索引將可使搜尋速度變快。如本主題前面所述，當您具有在 WHERE 子句中指定 exist() 方法的查詢時，主索引就非常有用。如果您加入 PATH 次要索引，也可以改善這類查詢的搜尋效能。

雖然主要 XML 索引可避免必須在執行階段切割 XML 二進位大型物件，但是它可能無法為以路徑運算式為基礎的查詢提供最佳的效能。由於會針對大型 XML 執行個體，循序搜尋與 XML 二進位大型物件相對應的主要 XML 索引中的所有資料列，因此循序搜尋可能會很慢。在此情況下，將次要索引建立在主要索引的路徑值與節點值上，將可大幅增加索引搜尋的速度。在 PATH 次要索引中，路徑與節點值都是索引鍵資料行，可在搜尋路徑時能進行更有效率的搜尋。查詢最佳化工具可以針對如下列所示的運算式使用 PATH 索引：

  • 在 /root/Location 中只指定一個路徑

OR

  • 在 /root/Location/@LocationID[.="10"] 中，指定了路徑與節點值。

下列查詢顯示 PATH 索引非常有用：

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

在查詢中，exist() 方法中的路徑運算式 /PD:ProductDescription/@ProductModelID 及值 "19" 會對應至 PATH 索引的索引鍵欄位。這允許在 PATH 索引中進行直接搜尋，並對主索引中的路徑值提供比循序搜尋更佳的搜尋效能。

VALUE 次要 XML 索引

如果查詢是以值為基礎，例如，/Root/ProductDescription/@*[. = "Mountain Bike"] 或 //ProductDescription[@Name = "Mountain Bike"]，而且並未完整指定路徑，或是路徑中包含萬用字元，您可以透過建立次要 XML 索引 (在主要 XML 索引的節點值上建立)，以獲得更快的結果。

VALUE 索引的索引鍵資料行是主要 XML 索引的節點值與路徑。如果您的工作負載需要在不知道包含值的元素或屬性名稱的情況下，從 XML 執行個體查詢值，VALUE 索引將會非常有用。例如，下列運算式將可從擁有 VALUE 索引而獲益：

  • 在 //author[LastName="someName"] 中，您知道 <LastName> 元素的值，但是 <author> 父系可發生在任何位置。
  • 在 /book[@* = "someValue"] 中，查詢會尋找某些屬性中包含值 "someValue" 的 <book> 元素。

下列查詢會從 Contact 資料表傳回 ContactID。WHERE 子句可指定篩選，以便尋找 AdditionalContactInfoxml 類型資料行中的值。如果對應的其他連絡資訊 XML 二進位大型物件包含特定的電話號碼，就會傳回連絡識別碼。因為 <telephoneNumber> 元素有可能出現在 XML 的任何位置，所以路徑運算式會指定 descendent-or-self 軸。

WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)

SELECT ContactID 
FROM   Person.Contact
WHERE  AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1

在此情況下，雖然已得知 <number> 的搜尋值，但它有可能以 <telephoneNumber> 元素的子系出現在 XML 執行個體中的任何位置。此類的查詢可從以特定值為基礎的索引查閱獲益。

PROPERTY 次要索引

從個別 XML 執行個體擷取一或多個值的查詢可從 PROPERTY 索引獲益。當您使用 xml 類型的 value() 方法來擷取物件屬性，以及當物件的主索引鍵值為已知時，就會發生此情況。

PROPERTY 索引是建立在主要 XML 索引的資料行 (PK、Path 以及節點值) 上，在主要 XML 索引中 PK 是基底資料表的主索引鍵。

例如，若為產品型號 19，下列查詢就會使用 value() 方法來擷取 ProductModelID 和 ProductModelName 屬性值。PROPERTY 索引可提供比使用主要 XML 索引或其他次要 XML 索引更快的執行。

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,
       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName        
FROM Production.ProductModel   
WHERE ProductModelID = 19

除了本主題後面所述的差異之外，在 xml 類型資料行上建立 XML 索引與在非 xml 類型資料行上建立索引很相似。下列 Transact-SQL DDL 陳述式可用以建立及管理 XML 索引：

建立主要 XML 索引

若要建立主要 XML 索引，請使用 CREATE PRIMARY XML INDEX Transact-SQL DDL 陳述式。並非所有非 XML 索引可使用的選項在 XML 索引中都支援。

在建立 XML 索引時請注意下列項目：

  • 若要建立主要 XML 索引，包含要索引的 XML 資料行之資料表 (稱為基底資料表)，必須在主索引鍵上有叢集索引。這將可確保如果基底資料表已分割，可使用相同的資料分割配置與資料分割函數來分割主要 XML 索引。
  • 如果 XML 索引存在，將無法修改資料表的叢集索引鍵、主索引鍵。您必須在修改主索引鍵前，先卸除資料表上的所有 XML 索引。
  • 在單一 xml 類型資料行上可建立主要 XML 索引。您無法以作為索引鍵資料行的 XML 類型資料行，建立任何其他類型的索引。不過，您可以在非 XML 索引中包含 xml L 類型資料行。在資料表中的每個 xml 類型資料行都有其自己的主要 XML索引。不過，每個 xml 類型資料行只允許一個主要 XML 索引。
  • XML 索引是存在於與非 XML 索引相同的命名空間中。因此，在相同名稱的相同資料表上將無法同時擁有 XML 索引與非 XML 索引。
  • IGNORE_DUP_KEY 與 ONLINE 選項永遠為 XML 索引設定為 OFF。您可用 OFF 的值指定這些選項。
  • 使用者資料表的檔案群組或資料分割資訊可套用於 XML 索引。使用者無法在 XML 索引上分開指定這些選項。
  • DROP_EXISTING 索引選項可卸除主要 XML 索引並建立新的主要 XML 索引，或是卸除次要 XML 索引並建立新的次要 XML 索引。不過，這個選項無法卸除次要 XML 索引以建立新主要 XML 索引，反之亦然。
  • 主要 XML 索引名稱的限制與檢視名稱的限制相同。

您無法在檢視中的 xml 類型資料行、在具有 xml 類型資料行的 table 值變數或是在 xml 類型變數上建立 XML 索引。

  • 若要使用 ALTER TABLE ALTER COLUMN 選項，將 xml 類型資料行從不具類型變更為具類型的 XML (反之亦然)，在資料行上就不應存在 XML 索引。如果 XML 索引確實存在，必須在嘗試變更資料行類型前先卸除它。
  • 在建立 XML 索引時，必須將 ARITHABORT 選項設定為 ON。若要使用 XML 資料類型方法查詢、插入、刪除或更新 XML 資料行中的值，必須在連接上設定相同的選項。若未設定，XML 資料類型方法將會失敗。
    ms191497.note(zh-tw,SQL.90).gif附註：
    在目錄檢視中可以找到關於 XML 索引的相關資訊。不過，並不支援 sp_helpindex。在本主題後面所提供的範例顯示如何查詢目錄檢視以尋找 XML 索引資訊。

建立次要 XML 索引

您可以使用 CREATE XML INDEX Transact-SQL DDL 陳述式來建立次要 XML 索引，並指定您所需的次要 XML 索引類型。

在建立次要 XML 索引時請注意下列項目：

  • 除了 IGNORE_DUP_KEY 與 ONLINE 之外，所有套用至非叢集索引的索引選項都可在次要 XML 索引上使用。對於次要 XML 索引，有兩個選項必須永遠設定為 OFF。
  • 次要索引會像主要 XML 索引一樣進行分割。
  • DROP_EXISTING 可以卸除使用者資料表上的次要索引，並在使用者資料表上建立其他次要索引。

您可以查詢 sys.xml_indexes 目錄檢視，以便擷取 XML 索引資訊。請注意，在 sys.xml_indexes 目錄檢視中的 secondary_type_desc 資料行會提供次要索引的類型：

SELECT  * 
FROM    sys.xml_indexes

在 secondary_type_desc 資料行中傳回的值可以是 NULL、PATH、VALUE 或 PROPERTY。對於主要 XML 索引，此值會以 NULL 傳回。

修改 XML 索引

ALTER INDEX Transact-SQL DDL 陳述式可用以修改現有 XML 與非 XML 索引。然而，並非所有的 ALTER INDEX 選項都可供 XML 索引使用。在修改 XML 索引時，下列選項是無效的：

  • 重建和設定選項 IGNORE_DUP_KEY 對於 XML 索引是無效的。對於次要 XML 索引，重建選項 ONLINE 必須設定為 OFF。在 ALTER INDEX 陳述式中不允許使用 DROP_EXISTING 選項。重建索引時，您必須按照＜設定選項 (XML 索引)＞中描述的方式來設定連接選項。
  • 在使用者資料表中對於主索引鍵條件約束的修改，並不會自動傳播至 XML 索引。使用者必須先卸除 XML 索引，再重新建立它們。
  • 如果指定了 ALTER INDEX ALL，它會同時套用至非 XML 與 XML 索引。索引選項可以指定為對於兩種索引類型都無效。在此情況下，整個陳述式都會失敗。

卸除 XML 索引

DROP INDEX Transact-SQL 陳述式可用以卸除現有的主要或次要 XML 及非 XML 索引。不過，DROP INDEX 沒有任何選項會套用至 XML 索引。如果您卸除主要 XML 索引，也會刪除任何存在的次要索引。

具有 TableName**.**IndexName 的 DROP 語法已捨棄不用，XML 索引也不支援它。

範例

下列範例會顯示如何建立、修改和卸除 XML 索引。

A. 建立和卸除主要 XML 索引。

在下列範例中，會在 xml 類型資料行上建立 XML 索引。

DROP TABLE T
GO
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create Primary XML index 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Verify the index creation. 
-- Note index type is 3 for xml indexes.
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol' 
-- Drop the index.
DROP INDEX PIdx_T_XmlCol ON T

當卸除資料表時，也會自動卸除在該資料表上的所有 XML 索引。不過，如果在資料行上有 XML 索引，將無法從資料表卸除 XML 資料行。

在下列範例中，會在 xml 類型資料行上建立 XML 索引。如需詳細資訊，請參閱＜具類型與不具類型的 XML＞。

CREATE TABLE TestTable(
 Col1 int primary key, 
 Col2 xml (Production.ProductDescriptionSchemaCollection)) 
GO

現在，您可以在 Co12 上建立主要 XML索引。

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

B. 建立次要 XML 索引

下列範例說明如何建立次要 XML 索引。此範例也會顯示您已建立之 XML 索引的相關資訊。

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY
GO

您可以查詢 sys.xml_indexes 來擷取 XML 索引資訊。secondary_type_desc 資料行會提供次要索引類型。

SELECT  * 
FROM    sys.xml_indexes

您也可以查詢目錄檢視以便取得索引資訊。

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')

您可以加入範例資料，然後檢閱 XML 索引資訊。

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>')
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')
GO
-- Space usage of primary XML index
DECLARE @index_id int
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i 
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int
SELECT  @index_id = i.index_id 
FROM    sys.xml_indexes i 
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
 
-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.* 
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T
-- Drop table T.
DROP TABLE T
Go

C. 修改 XML 索引

在下列範例中，建立了 XML 索引，然後將 ALLOW_ROW_LOCKS 選項設定為 OFF，藉以進行修改。當 ALLOW_ROW_LOCKS 為 OFF 時，將不會鎖定資料列，並可使用頁面與資料表層級鎖定來取得指定索引的存取權。

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary XML index. 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'

-- Modify and set an index option.
ALTER INDEX PIdx_T_XmlCol on T 
SET (ALLOW_ROW_LOCKS = OFF)

D. 停用和啟用 XML 索引

依預設，XML 索引為已啟用。如果停用了 XML 索引，針對 XML 資料行執行的查詢將不會使用 XML 索引。若要啟用 XML 索引，請使用 ALTER INDEX 搭配 REBUILD 選項。

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)
GO
ALTER INDEX PIdx_T_XmlCol on T DISABLE
Go
-- Verify index is disabled.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'
-- Rebuild the index.
ALTER INDEX PIdx_T_XmlCol on T REBUILD
Go

E. 使用 DROP_EXISTING 索引選項建立 XML 索引

在下列範例中，會在資料行 (XmlColx) 上建立 XML 索引。接著，會在不同資料行 (XmlColy) 上建立另一個具有相同名稱的 XML 索引。因為指定了 DROP_EXISTING 選項，所以會卸除 (XmlColx) 上的現有 XML 索引，並在 (XmlColy) 上建立新 XML 索引。

DROP TABLE T
GO
CREATE TABLE T(Col1 int primary key, XmlColx xml, XmlColy xml)
GO
-- Create XML index on XmlColx.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColx)
GO
-- Create same name XML index on XmlColy.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColy) 
WITH (DROP_EXISTING = ON)
-- Verify the index is created on XmlColy.d.
SELECT sc.name 
FROM   sys.xml_indexes si inner join sys.index_columns sic 
ON     sic.object_id=si.object_id and sic.index_id=si.index_id
INNER  join sys.columns sc on sc.object_id=sic.object_id 
AND    sc.column_id=sic.column_id
WHERE  si.name='PIdx_T_XmlCol' 
AND    si.object_id=object_id('T')

此查詢會傳回已建立的指定 XML 索引之資料行名稱。

請參閱

概念

XML 資料類型
XML 應用程式範例

其他資源

sys.dm_db_index_physical_stats

說明及資訊

取得 SQL Server 2005 協助