CREATE XML INDEX (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

为指定的表创建 XML 索引。 可在向表中填入数据前创建索引。 可通过指定限定的数据库名称，为另一个数据库中的表创建 XML 索引。

注意

若要创建关系索引，请参阅 CREATE INDEX (Transact-SQL)。 有关如何创建空间索引的信息，请参阅 CREATE SPATIAL INDEX (Transact-SQL)。

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 }
}

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

参数

[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 计算列创建主 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 索引。

PATH
为基于主 XML 索引中的路径值和节点值生成的列创建辅助 XML 索引。 在 PATH 辅助索引中，路径值和节点值是用于提高路径搜索效率的键列。

PROPERTY
为 PK 为基表主键的主 XML 索引列（PK、路径值和节点值）创建辅助 XML 索引。

<object>::=

要为其建立索引的完全限定对象或非完全限定对象。

database_name
数据库的名称。

schema_name
表所属的架构的名称。

table_name
要编制索引的表的名称。

<xml_index_option> ::=

指定创建索引时要使用的选项。

PAD_INDEX = { ON | OFF }

指定索引填充。 默认为 OFF。

ON
fillfactor 指定的可用空间百分比应用于索引的中间级页。

OFF 或未指定 fillfactor
考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。

PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。 如果为 FILLFACTOR 指定的百分比不够大，无法容纳一行，数据库引擎将在内部覆盖该百分比以允许最小值。 无论 fillfactor 的值有多小，中间级索引页上的行数永远都不会小于两行。

FILLFACTOR = fillfactor

指定一个百分比，指示在数据库引擎创建或重新生成索引的过程中，应将每个索引页面的叶级填充到什么程度。 fillfactor 必须是 1 到 100 之间的整数。 默认值为 0。 如果 fillfactor 为 100 或 0，数据库引擎会创建完全填充叶级页的索引。

注意

填充因子的值 0 和 100 在所有方面都是相同的。

FILLFACTOR 设置仅在创建或重新生成索引时应用。 数据库引擎并不会在页中动态保持指定的可用空间百分比。 若要查看填充因子设置，请使用 sys.indexes 目录视图。

重要

使用低于 100 的 FILLFACTOR 值创建聚集索引会影响数据占用的存储空间量，因为数据库引擎在创建聚集索引时会重新分布数据。

有关详细信息，请参阅 为索引指定填充因子。

SORT_IN_TEMPDB = { ON | OFF }

指定是否在 tempdb 中存储临时排序结果。 默认为 OFF。

ON
在 tempdb 中存储用于生成索引的中间排序结果。 如果 tempdb 与用户数据库不在同一组磁盘上，就可缩短创建索引所需的时间。 但是，这会增加索引生成期间所使用的磁盘空间量。

OFF
中间排序结果与索引存储在同一数据库中。

除在用户数据库中创建索引所需的空间外，tempdb 还必须有大约相同的额外空间来存储中间排序结果。 有关详细信息，请参阅用于索引的 SORT_IN_TEMPDB 选项。

IGNORE_DUP_KEY = OFF

对 XML 索引不起作用，这是因为此索引类型永远不唯一。 请不要将此选项设置为 ON，否则会引发错误。

DROP_EXISTING = { ON | OFF }

指定删除并重新生成已命名的先前存在的 XML 索引。 默认为 OFF。

ON
删除并重新生成现有索引。 指定的索引名称必须与当前的现有索引相同；但可以修改索引定义。 例如，可以指定不同的列、排序顺序、分区方案或索引选项。

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。

ON
在访问索引时允许使用行锁。 数据库引擎确定何时使用行锁。

OFF
不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

指定是否允许使用页锁。 默认值为 ON。

ON
在访问索引时允许使用页锁。 数据库引擎确定何时使用页锁。

OFF
不使用页锁。

MAXDOP = max_degree_of_parallelism

在索引操作期间替代配置 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) 中的“注释”部分。

示例

本文需要 AdventureWorks2022 示例数据库，其可从 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

另请参阅