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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse 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 索引或辅助 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_name index_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_name xml_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_name USING 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.

ValueVALUE
为主 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
为 PK 为基表主键的主 XML 索引列(PK、路径值和节点值)创建辅助 XML 索引。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.

ONON
fillfactor 指定的可用空间百分比应用于索引的中间级页 。The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF 或未指定 fillfactor OFF 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.

PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 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 Engine将在内部覆盖该百分比以允许最小值。If the percentage specified for FILLFACTOR is not large enough to allow for one row, the 数据库引擎Database 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 =fillfactor FILLFACTOR =fillfactor
指定一个百分比,指示在数据库引擎Database Engine创建或重新生成索引的过程中,应将每个索引页面的叶级填充到什么程度。Specifies a percentage that indicates how full the 数据库引擎Database 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 Engine会创建完全填充叶级页的索引 。If fillfactor is 100 or 0, the 数据库引擎Database 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 Engine并不会在页中动态保持指定的可用空间百分比。The 数据库引擎Database 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 Engine在创建聚集索引时会重新分布数据。Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the 数据库引擎Database 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.

ONON
在 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 =OFF IGNORE_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.

ONON
删除并重新生成现有索引。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 =OFF ONLINE =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.

ONON
在访问索引时允许使用行锁。Row locks are allowed when accessing the index. 数据库引擎Database Engine确定何时使用行锁。The 数据库引擎Database 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.

ONON
在访问索引时允许使用页锁。Page locks are allowed when accessing the index. 数据库引擎Database Engine确定何时使用页锁。The 数据库引擎Database Engine determines when page locks are used.

OFFOFF
不使用页锁。Page locks are not used.

MAXDOP =max_degree_of_parallelism MAXDOP =max_degree_of_parallelism
在索引操作期间覆盖配置 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.

RemarksRemarks

可以对从 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)