共用方式為


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 資料表傳回 ContactIDWHERE 子句可指定篩選,以便尋找 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() 方法來擷取 ProductModelIDProductModelName 屬性值。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_LOCKSOFF 時,將不會鎖定資料列,並可使用頁面與資料表層級鎖定來取得指定索引的存取權。

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 協助