CREATE XML INDEX (Transact-SQL)

文章
12/22/2023

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

在指定的資料表上建立 XML 索引。可以在資料表中有資料之前建立索引。指定限定的資料庫名稱，就可以在另一個資料庫的資料表上建立 XML 索引。

注意

若要建立關聯式索引，請參閱 CREATE INDEX (Transact-SQL)。如需如何建立空間索引的相關資訊，請參閱 CREATE SPATIAL INDEX (Transact-SQL)。

語法

--Create XML Index   
CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ database_name.schema_name.table_name | schema_name.table_name | table_name }

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | XML_COMPRESSION = { ON | OFF }
}

引數

[PRIMARY] XML

在指定的 xml 資料行上建立 XML 索引。當指定 PRIMARY 時，會利用使用者資料表和 XML 節點識別碼所構成的叢集索引鍵來建立叢集索引。每一份資料表最多可以有 249 個 XML 索引。建立 XML 索引時，請注意下列事項：

叢集索引必須存在於使用者資料表的主索引鍵上。
使用者資料表的叢集索引鍵限定為 15 個資料行。
資料表中的每一個 xml 資料行都可以有一個主要 XML 索引和多個次要 XML 索引。
主要 XML 索引必須先存在於 xml 資料行上，才能在該資料行上建立次要 XML 索引。
XML 索引只能在單一 xml 資料行上建立。您無法在非 xml 資料行上建立 XML 索引，也無法在 xml 資料行上建立關聯式索引。
在檢視的 xml 資料行上、在含有 xml 資料行的資料表值變數上，或在 xml 類型變數上，您都無法建立 XML 索引 (不論是主要還是次要)。
您無法在計算的 xml 資料行上建立主要 XML 索引。
SET 選項設定必須與索引檢視表和計算資料行索引所需的設定相同。具體而言，在建立 XML 索引時，以及在插入、刪除或更新 xml 資料行中的值時，必須將 ARITHABORT 選項設為 ON。

如需詳細資訊，請參閱 XML 索引 (SQL Server)。

index_name

索引的名稱。索引名稱在資料表中必須是唯一，但在資料庫中不一定要是唯一的。索引名稱必須遵照識別碼的規則。

主要 XML 索引名稱的開頭不能是下列字元：#、##、@ 或 @@。

xml_column_name

當做索引根據的 xml 資料行。在單一 XML 索引定義中，只能指定一個 xml 資料行；但是在 xml 資料行上則可以建立多個次要 XML 索引。

USING XML INDEX xml_index_name

指定主要 XML 索引，以便用來建立次要 XML 索引。

FOR { VALUE | PATH | PROPERTY }

指定次要 XML 索引的類型。

值
在索引鍵資料行屬於主要 XML 索引 (屬於主要 XML 索引的節點值和路徑) 的資料行上建立次要 XML 索引。

PATH
在建立於主要 XML 索引中之路徑值和節點值上的資料行上建立次要 XML 索引。在 PATH 次要索引中，路徑值和節點值是指在搜尋路徑時允許有效搜尋的索引鍵資料行。

PROPERTY
在主要 XML 索引的資料行 (PK、路徑和節點值) 上建立次要 XML 索引 (此主要 XML 索引的 PK 是基底資料表的主索引鍵)。

<object>::=

要編製索引的完整或非完整物件。

database_name
資料庫的名稱。

schema_name
資料表所屬的結構描述名稱。

table_name
要編製索引的資料表名稱。

<xml_index_option> ::=

指定當您建立索引時所需使用的選項。

PAD_INDEX = { ON | OFF }

指定索引填補。預設值為 OFF。

開啟
fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面。

OFF 或未指定 fillfactor
中繼層級頁面會幾乎填滿整個容量，但會考量中繼頁面上的索引鍵集，而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。

只有在指定 FILLFACTOR 時，才能使用 PAD_INDEX 選項，因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。如果針對 FILLFACTOR 指定的百分比不夠大，無法允許一個資料列，資料庫引擎就會在內部覆寫該百分比以允許最小值。不論 fillfactor 的值設得多低，中繼索引頁面上的資料列數目絕對不能少於兩個。

FILLFACTOR = fillfactor

指定百分比，以表示 Database Engine 在索引建立或重建期間應該將每個索引頁面的分葉層級填滿的程度。 fillfactor 必須是 1 到 100 之間的整數值。預設值是 0。如果 fillfactor 是 100 或 0，資料庫引擎就會利用已填滿容量的分葉頁面來建立索引。

注意

填滿因數值 0 和 100 在各方面都是一樣的。

只有在建立或重建索引時才會套用 FILLFACTOR 設定。資料庫引擎不會動態保留頁面中空白空間的指定百分比。若要檢視填滿因數設定，請使用 sys.indexes 目錄檢視表。

重要

使用小於 100 的 FILLFACTOR 來建立叢集索引，會影響資料佔用的儲存空間數量，因為資料庫引擎在建立叢集索引時會轉散發資料。

如需詳細資訊，請參閱指定索引的填滿因素。

SORT_IN_TEMPDB = { ON | OFF }

指定是否要將暫時排序結果儲存在 tempdb 中。預設值為 OFF。

開啟
用來建置索引的中繼排序結果會儲存在 tempdb 中。如果 tempdb 位於與使用者資料庫所在磁碟不同的磁碟上，這可能會減少建立索引所需的時間。不過，這會增加建立索引時所使用的磁碟空間量。

OFF
中繼排序結果會儲存在與用來儲存索引相同的資料庫中。

除了建立索引時使用者資料庫中所需的空間以外，tempdb 還需要大約相同數量的額外空間來容納中繼排序結果。如需詳細資訊，請參閱索引的 SORT_IN_TEMPDB 選項。

IGNORE_DUP_KEY = OFF

對於 XML 索引沒有任何作用，因為索引類型絕對不是唯一的。請勿將此選項設定為 ON，否則會引發錯誤。

DROP_EXISTING = { ON | OFF }

指定要卸除及重建預先存在的具名 XML 索引。預設值為 OFF。

開啟
卸除及重建現有的索引。所指定的索引名稱必須與目前現有的索引相同；不過，索引定義可以修改。例如，您可以指定不同的資料行、排序次序、分割區配置或索引選項。

OFF
如果所指定的索引名稱已存在，畫面上會出現錯誤。

您無法利用 DROP_EXISTING 來變更索引類型。另外，主要 XML 索引無法重新定義為次要 XML 索引，反之亦然。

ONLINE = OFF

指定在編製索引作業期間，基礎資料表和相關聯的索引無法供查詢和資料修改使用。在這一版的 SQL Server 中，不支援針對 XML 索引進行線上編製索引。如果針對 XML 索引將此選項設定為 ON，就會引發錯誤。請省略 ONLINE 選項，或是將 ONLINE 設定為 OFF。

建立、重建或卸除 XML 索引的離線編製索引作業會取得資料表的結構描述修改 (Sch-M) 鎖定。這可防止所有使用者在作業期間存取基礎資料表。

注意

並非所有 Microsoft SQL Server 版本都提供線上編製索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

ALLOW_ROW_LOCKS = { ON | OFF }

指定是否允許資料列鎖定。預設值為 ON。

開啟
當存取索引時，允許資料列鎖定。資料庫引擎會決定使用資料列鎖定的時機。

OFF
不會使用資料列鎖定。

ALLOW_PAGE_LOCKS = { ON | OFF }

指定是否允許頁面鎖定。預設值為 ON。

開啟
當存取索引時，允許頁面鎖定。資料庫引擎會決定使用頁面鎖定的時機。

OFF
不會使用頁面鎖定。

MAXDOP = max_degree_of_parallelism

在編製索引作業期間，覆寫設定平行處理原則的最大程度伺服器組態選項。請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。最大值是 64 個處理器。

重要

雖然所有 XML 索引在語法上都支援 MAXDOP 選項，但是對於主要 XML 索引而言，CREATE XML INDEX 只會使用單一處理器。

max_degree_of_parallelism 可以是：

1
隱藏平行計畫的產生。

>1
根據目前的系統工作負載，將平行索引作業所使用的處理器數目上限，限制為所指定的數目或更少的數目。

0 (預設值)
根據目前的系統工作負載，使用實際數目或比實際數目更少的處理器。

如需詳細資訊，請參閱設定平行索引作業。

注意

並非所有 Microsoft SQL Server 版本都提供平行編製索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

備註

只要計算資料行資料類型可當做索引鍵資料行或非索引鍵資料行，衍生自 xml 資料類型的計算資料行都可以當做索引鍵資料行或內含非索引鍵資料行來建立索引。您無法在計算的 xml 資料行上建立主要 XML 索引。

若要檢視關於 XML 索引的資訊，請使用 sys.xml_indexes 目錄檢視。

如需 XML 索引的詳細資訊，請參閱 XML 索引 (SQL Server)。

XML 壓縮

適用於：SQL Server 2022 (16.x) 和更新版本、Azure SQL 資料庫，以及 Azure SQL 受控執行個體。

XML 索引不會繼承資料表的壓縮屬性。若要壓縮索引，您必須在 XML 索引上明確啟用 XML 壓縮。
次要 XML 索引不會繼承主要 XML 索引的壓縮屬性。
根據預設，在建立索引時，XML 索引的 XML 壓縮設定會設定為 OFF。

其他有關索引建立的備註

如需索引建立的詳細資訊，請參閱 CREATE INDEX (Transact-SQL) 中的＜備註＞一節。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 建立主要 XML 索引

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立主要 XML 索引。

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);  
GO

B. 使用 XML 壓縮建立主要 XML 索引

適用於：SQL Server 2022 (16.x) 和更新版本、Azure SQL 資料庫，以及 Azure SQL 受控執行個體。

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立主要 XML 索引。

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription)
    WITH (XML_COMPRESSION = ON);  
GO

C. 建立次要 XML 索引

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立次要 XML 索引。

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

D. 使用 XML 壓縮建立次要 XML 索引

適用於：SQL Server 2022 (16.x) 和更新版本、Azure SQL 資料庫，以及 Azure SQL 受控執行個體。

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立次要 XML 索引。

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH
    WITH (XML_COMPRESSION = ON);
GO

分享方式：

CREATE XML INDEX (Transact-SQL)

語法

引數

[PRIMARY] XML

index_name

xml_column_name

USING XML INDEX xml_index_name

FOR { VALUE | PATH | PROPERTY }

<object>::=

<xml_index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | OFF }

IGNORE_DUP_KEY = OFF

DROP_EXISTING = { ON | OFF }

ONLINE = OFF

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

MAXDOP = max_degree_of_parallelism

備註

XML 壓縮

其他有關索引建立的備註

範例

A. 建立主要 XML 索引

B. 使用 XML 壓縮建立主要 XML 索引

C. 建立次要 XML 索引

D. 使用 XML 壓縮建立次要 XML 索引

另請參閱

意見反映

更多資源