CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

在指定的資料表上建立 XML 索引。Creates an XML index on a specified table. 可以在資料表中有資料之前建立索引。An index can be created before there is data in the table. 指定限定的資料庫名稱,就可以在另一個資料庫的資料表上建立 XML 索引。XML indexes can be created on tables in another database by specifying a qualified database name.

注意

若要建立關聯式索引,請參閱 CREATE INDEX (Transact-SQL)To create a relational index, see CREATE INDEX (Transact-SQL). 如需有關如何建立空間索引的詳細資訊,請參閱 CREATE SPATIAL INDEX (Transact-SQL)For information about how to create a spatial index, see CREATE SPATIAL INDEX (Transact-SQL).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
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  
}  
  

引數Arguments

[PRIMARY] XML[PRIMARY] XML
在指定的 xml 資料行上建立 XML 索引。Creates an XML index on the specified xml column. 當指定 PRIMARY 時,會利用使用者資料表和 XML 節點識別碼所構成的叢集索引鍵來建立叢集索引。When PRIMARY is specified, a clustered index is created with the clustered key formed from the clustering key of the user table and an XML node identifier. 每一份資料表最多可以有 249 個 XML 索引。Each table can have up to 249 XML indexes. 建立 XML 索引時,請注意下列事項:Note the following when you create an XML index:

  • 叢集索引必須存在於使用者資料表的主索引鍵上。A clustered index must exist on the primary key of the user table.

  • 使用者資料表的叢集索引鍵限定為 15 個資料行。The clustering key of the user table is limited to 15 columns.

  • 資料表中的每一個 xml 資料行都可以有一個主要 XML 索引和多個次要 XML 索引。Each xml column in a table can have one primary XML index and multiple secondary XML indexes.

  • 主要 XML 索引必須先存在於 xml 資料行上,才能在該資料行上建立次要 XML 索引。A primary XML index on an xml column must exist before a secondary XML index can be created on the column.

  • XML 索引只能在單一 xml 資料行上建立。An XML index can only be created on a single xml column. 您無法在非 xml 資料行上建立 XML 索引,也無法在 xml 資料行上建立關聯式索引。You cannot create an XML index on a non-xml column, nor can you create a relational index on an xml column.

  • 在檢視表的 xml 資料行上、在含有 xml 資料行之資料表值的變數上,或在 xml 類型變數上,您都無法建立 XML 索引 (不論是主要還是次要)。You cannot create an XML index, either primary or secondary, on an xml column in a view, on a table-valued variable with xml columns, or xml type variables.

  • 您無法在計算的 xml 資料行上建立主要 XML 索引。You cannot create a primary XML index on a computed xml column.

  • SET 選項設定必須與索引檢視表和計算資料行索引所需的設定相同。The SET option settings must be the same as those required for indexed views and computed column indexes. 具體而言,在建立 XML 索引時,以及在插入、刪除或更新 xml 資料行中的值時,必須將 ARITHABORT 選項設為 ON。Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column.

如需詳細資訊,請參閱 XML 索引 (SQL Server)For more information, see XML Indexes (SQL Server).

index_nameindex_name
這是索引的名稱。Is the name of the index. 索引名稱在資料表中必須是唯一的,但是在資料庫中不需要是唯一的。Index names must be unique within a table but do not have to be unique within a database. 索引名稱必須遵照識別碼的規則。Index names must follow the rules of identifiers.

主要 XML 索引名稱的開頭不能是下列字元: ###@@@Primary XML index names cannot start with the following characters: #, ##, @, or @@.

xml_column_namexml_column_name
這是當做索引根據的 xml 資料行。Is the xml column on which the index is based. 在單一 XML 索引定義中,只能指定一個 xml 資料行;但是在 xml 資料行上則可以建立多個次要 XML 索引。Only one xml column can be specified in a single XML index definition; however, multiple secondary XML indexes can be created on an xml column.

USING XML INDEX xml_index_nameUSING XML INDEX xml_index_name
指定主要 XML 索引,以便用來建立次要 XML 索引。Specifies the primary XML index to use in creating a secondary XML index.

FOR { VALUE | PATH | PROPERTY }FOR { VALUE | PATH | PROPERTY }
指定次要 XML 索引的類型。Specifies the type of secondary XML index.

VALUE
在索引鍵資料行屬於主要 XML 索引 (屬於主要 XML 索引的節點值和路徑) 的資料行上建立次要 XML 索引。Creates a secondary XML index on columns where key columns are (node value and path) of the primary XML index.

PATHPATH
在建立於主要 XML 索引中之路徑值和節點值上的資料行上建立次要 XML 索引。Creates a secondary XML index on columns built on path values and node values in the primary XML index. 在 PATH 次要索引中,路徑值和節點值是指在搜尋路徑時允許有效搜尋的索引鍵資料行。In the PATH secondary index, the path and node values are key columns that allow efficient seeks when searching for paths.

PROPERTYPROPERTY
在主要 XML 索引的資料行 (PK、路徑和節點值) 上建立次要 XML 索引 (此主要 XML 索引的 PK 是基底資料表的主索引鍵)。Creates a secondary XML index on columns (PK, path and node value) of the primary XML index where PK is the primary key of the base table.

<object>::=<object>::=

這是要建立索引的完整或非完整物件。Is the fully qualified or nonfully qualified object to be indexed.

database_namedatabase_name
這是資料庫的名稱。Is the name of the database.

schema_nameschema_name
這是資料表所屬的結構描述名稱。Is the name of the schema to which the table belongs.

table_nametable_name
這是要建立索引的資料表名稱。Is the name of the table to be indexed.

<xml_index_option> ::=<xml_index_option> ::=

指定當您建立索引時所需使用的選項。Specifies the options to use when you create the index.

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }
指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

開啟ON
fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面。The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF 或未指定 fillfactorOFF or fillfactor is not specified
中繼層級頁面會幾乎填滿整個容量,但會考量中繼頁面上的索引鍵集,而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

只有在指定 FILLFACTOR 時,才能使用 PAD_INDEX 選項,因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. 如果 FILLFACTOR 所指定的百分比不夠,無法允許一個資料列,Database EngineDatabase Engine 會在內部覆寫該百分比以允許最小值。If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database EngineDatabase Engine internally overrides the percentage to allow for the minimum. 不論 fillfactor 的值設得多低,中繼索引頁面上的資料列數目絕對不能少於兩個。The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

FILLFACTOR = fillfactorFILLFACTOR =fillfactor
指定用以指出建立或重建索引時,Database EngineDatabase Engine 填滿各索引頁面分葉層級之程度的百分比。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or rebuild. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 預設值是 0。The default is 0. 如果 fillfactor 是 100 或 0, Database EngineDatabase Engine 會利用已填滿容量的分葉頁面來建立索引。If fillfactor is 100 or 0, the Database EngineDatabase Engine creates indexes with leaf pages filled to capacity.

注意

填滿因數值 0 和 100 在各方面都是一樣的。Fill factor values 0 and 100 are the same in all respects.

只有在建立或重建索引時才會套用 FILLFACTOR 設定。The FILLFACTOR setting applies only when the index is created or rebuilt. Database EngineDatabase Engine 不會動態保留頁面中空白空間的指定百分比。The Database EngineDatabase Engine does not dynamically keep the specified percentage of empty space in the pages. 若要檢視填滿因數設定,請使用 sys.indexes 目錄檢視表。To view the fill factor setting, use the sys.indexes catalog view.

重要

利用小於 100 的 FILLFACTOR 來建立叢集索引,會影響資料所佔用的儲存空間數量,因為 Database EngineDatabase Engine 在建立叢集索引時會轉散發資料。Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database EngineDatabase Engine redistributes the data when it creates the clustered index.

如需詳細資訊,請參閱 指定索引的填滿因素For more information, see Specify Fill Factor for an Index.

SORT_IN_TEMPDB = { ON | OFF }SORT_IN_TEMPDB = { ON | OFF }
指定是否要將暫時排序結果儲存在 tempdb 中。Specifies whether to store temporary sort results in tempdb. 預設值為 OFF。The default is OFF.

開啟ON
用來建置索引的中繼排序結果會儲存在 tempdb 中。The intermediate sort results that are used to build the index are stored in tempdb. 如果 tempdb 位於與使用者資料庫所在磁碟不同的磁碟上,這可能會減少建立索引所需的時間。This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. 不過,這會增加建立索引時所使用的磁碟空間量。However, this increases the amount of disk space that is used during the index build.

OFFOFF
中繼排序結果會儲存在與用來儲存索引相同的資料庫中。The intermediate sort results are stored in the same database as the index.

除了建立索引時使用者資料庫中所需的空間以外,tempdb 還需要大約相同數量的額外空間來容納中繼排序結果。In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. 如需詳細資訊,請參閱索引的 SORT_IN_TEMPDB 選項For more information, see SORT_IN_TEMPDB Option For Indexes.

IGNORE_DUP_KEY =OFFIGNORE_DUP_KEY =OFF
對於 XML 索引沒有任何作用,因為索引類型絕對不是唯一的。Has no effect for XML indexes because the index type is never unique. 請勿將這個選項設定為 ON,否則會引發錯誤。Do not set this option to ON, or else an error is raised.

DROP_EXISTING = { ON | OFF }DROP_EXISTING = { ON | OFF }
指定要卸除及重建預先存在的具名 XML 索引。Specifies that the named, preexisting XML index is dropped and rebuilt. 預設值為 OFF。The default is OFF.

開啟ON
卸除及重建現有的索引。The existing index is dropped and rebuilt. 所指定的索引名稱必須與目前現有的索引相同;不過,索引定義可以修改。The index name specified must be the same as a currently existing index; however, the index definition can be modified. 例如,您可以指定不同的資料行、排序次序、分割區配置或索引選項。For example, you can specify different columns, sort order, partition scheme, or index options.

OFFOFF
如果所指定的索引名稱已存在,畫面上會出現錯誤。An error is displayed if the specified index name already exists.

您無法利用 DROP_EXISTING 來變更索引類型。The index type cannot be changed by using DROP_EXISTING. 另外,主要 XML 索引無法重新定義為次要 XML 索引,反之亦然。Also, a primary XML index cannot be redefined as a secondary XML index, or vice versa.

ONLINE =OFFONLINE =OFF
指定在索引作業期間,基礎資料表和相關聯的索引無法供查詢和資料修改使用。Specifies that underlying tables and associated indexes are not available for queries and data modification during the index operation. 在這一版的 SQL ServerSQL Server 中,XML 索引不支援線上索引建立。In this version of SQL ServerSQL Server, online index builds are not supported for XML indexes. 如果此選項針對 XML 索引設定為 ON,就會引發錯誤。If this option is set to ON for a XML index, an error is raised. 請省略 ONLINE 選項,或是將 ONLINE 設定為 OFF。Either omit the ONLINE option or set ONLINE to OFF.

建立、重建或卸除 XML 索引的離線索引作業會取得資料表的結構描述修改 (Sch-M) 鎖定。An offline index operation that creates, rebuilds, or drops a XML index, acquires a Schema modification (Sch-M) lock on the table. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation.

注意

MicrosoftMicrosoftSQL ServerSQL Server 的所有版本都無法使用線上索引作業。Online index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當存取索引時,允許資料列鎖定。Row locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用資料列鎖定的時機。The Database EngineDatabase Engine determines when row locks are used.

OFFOFF
不使用資料列鎖定。Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當存取索引時,允許頁面鎖定。Page locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用頁面鎖定的時機。The Database EngineDatabase Engine determines when page locks are used.

OFFOFF
不使用頁面鎖定。Page locks are not used.

MAXDOP = max_degree_of_parallelismMAXDOP =max_degree_of_parallelism
針對索引作業期間,覆寫設定平行處理原則的最大程度伺服器組態選項組態選項。Overrides the Configure the max degree of parallelism Server Configuration Option configuration option for the duration of the index operation. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

重要

雖然所有 XML 索引在語法上都支援 MAXDOP 選項,但是對於主要 XML 索引而言,CREATE XML INDEX 只會使用單一處理器。Although the MAXDOP option is syntactically supported for all XML indexes, for a primary XML index, CREATE XML INDEX uses only a single processor.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
根據目前的系統工作負載,將平行索引作業所使用的處理器數目上限,限制為所指定的數目或更少的數目。Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

MicrosoftMicrosoftSQL ServerSQL Server 的每個版本都無法使用平行索引作業。Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

備註Remarks

只要計算資料行資料類型可當做索引鍵資料行或非索引鍵資料行,衍生自 xml 資料類型的計算資料行都可以當做索引鍵資料行或內含非索引鍵資料行來建立索引。Computed columns derived from xml data types can be indexed either as a key or included nonkey column as long as the computed column data type is allowable as an index key column or nonkey column. 您無法在計算的 xml 資料行上建立主要 XML 索引。You cannot create a primary XML index on a computed xml column.

若要檢視關於 XML 索引的資訊,請使用 sys.xml_indexes 目錄檢視。To view information about XML indexes, use the sys.xml_indexes catalog view.

如需 XML 索引的詳細資訊,請參閱 XML 索引 (SQL Server)For more information about XML indexes, see XML Indexes (SQL Server).

有關索引建立的其他備註Additional Remarks on Index Creation

如需有關索引建立的詳細資訊,請參閱 CREATE INDEX (Transact-SQL) 中的<備註>一節。For more information about index creation, see the "Remarks" section in CREATE INDEX (Transact-SQL).

範例Examples

A.A. 建立主要 XML 索引Creating a primary XML index

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立主要 XML 索引。The following example creates a primary XML index on the CatalogDescription column in the Production.ProductModel table.

USE AdventureWorks2012;  
GO  
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.B. 建立次要 XML 索引Creating a secondary XML index

下列範例會在 CatalogDescription 資料表的 Production.ProductModel 資料行上建立次要 XML 索引。The following example creates a secondary XML index on the CatalogDescription column in the Production.ProductModel table.

USE AdventureWorks2012;  
GO  
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  

另請參閱See Also

ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL) CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
資料類型 (Transact-SQL) Data Types (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
XML 索引 (SQL Server) XML Indexes (SQL Server)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL) sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL) sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
XML 索引 (SQL Server)XML Indexes (SQL Server)