CREATE INDEX (Transact-SQL)

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

为表或视图创建相关索引。 也称为行存储索引，因为它可能是聚集或非聚集的 B 树索引。 可以在表中不存在数据时创建行存储索引。 使用行存储索引提高查询性能，尤其是在查询从特定列中进行选择或需要按特定顺序对值进行排序时。

注意

文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，数据库引擎实现了 B+ 树。 这不适用于列存储索引或内存优化表上的索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

Azure Synapse Analytics 和 Analytics Platform System (PDW) 目前不支持唯一约束。 引用唯一约束的任何示例仅适用于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例。

有关索引设计指南的信息，请参阅 SQL Server 索引设计指南。

示例：

1. 对表或视图创建非聚集索引

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. 在表上创建聚集索引，并为表使用由 3 个部分组成的名称

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. 使用唯一约束创建非聚集索引并指定排序顺序

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

主要方案：

从 Azure SQL 数据库和 Azure SQL 托管实例中的 SQL Server 2016（13.x）开始，可以在列存储索引上使用非聚集索引来提高数据仓库查询性能。 有关详细信息，请参阅 列存储索引 - 数据仓库。

有关其他类型的索引，请参阅：

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Transact-SQL 语法约定

语法

适用于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例的语法

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

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

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

后向兼容的关系索引

重要

在 SQL Server 的未来版本中，将删除此后向兼容的关系索引语法结构。 请避免在新的开发工作中使用此语法结构，并计划修改当前使用此功能的应用程序。 改用 <relational_index_option> 中指定的语法结构。

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Azure Synapse Analytics 和并行数据仓库的语法

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

参数

UNIQUE

为表或视图创建唯一索引。 唯一索引不允许两行具有相同的索引键值。

数据库引擎不允许对已包含重复值的列创建唯一索引，无论是否 IGNORE_DUP_KEY 设置为 ON。 如果尝试执行此作，数据库引擎会显示一条错误消息。 必须先删除重复值，然后才能为一列或多列创建唯一索引。

UNIQUE约束被视为NULL值。 如果列可为 null，并且 UNIQUE 该列上存在约束，则最多允许有一 NULL 行。

CLUSTERED

创建索引，其中为索引键列指定的排序顺序决定了磁盘上的索引结构中的页顺序。 聚集索引底部或叶级页中的行始终包含表的所有列。 索引上层页面上的行仅包含键列。

一个表只能包含一个聚集索引。 如果表上存在聚集索引，则它包含表中的所有数据。 没有聚集索引的表称为堆。

具有唯一聚集索引的视图称为索引视图。 索引视图只能有一个聚集索引。 为一个视图创建唯一聚集索引会在物理上具体化该视图。 必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引。 有关详细信息，请参阅 “创建索引视图”。

在创建任何非聚集索引之前创建聚集索引。 创建聚集索引时，将重新生成表上的现有非聚集索引，如果表很大，则这是资源密集型作。

如果没有指定 CLUSTERED，则创建非聚集索引。

注意

由于聚集索引包含表中的所有数据，因此创建聚集索引并使用 ON partition_scheme_name 或 ON filegroup_name 子句有效地将表从创建表的文件组移动到新的分区方案或文件组。 对特定的文件组创建表或索引之前，应确认哪些文件组可用并且有足够的空间供索引使用。

在某些情况下，创建聚集索引可以启用以前禁用的索引。 有关详细信息，请参阅 “启用索引和约束 ”和 “禁用索引和约束”。

NONCLUSTERED

创建索引，其中为索引键列指定的排序顺序决定了磁盘上的索引结构中的页顺序。 与聚集索引不同，非聚集索引叶级别的页面上的行仅包含索引键列。 （可选）可以使用子句包含 INCLUDE 非键列的子集。

无论如何创建索引，每个表最多可以有 999 个非聚集索引：隐式使用 PRIMARY KEY 和 UNIQUE 约束，或者显式使用 CREATE INDEX。

对于索引视图，只能为已定义唯一聚集索引的视图创建非聚集索引。

如果未另行指定，默认索引类型则为非聚集。

index_name

索引的名称。 索引名称在表或视图中必须唯一，但在数据库中不必唯一。 索引名称必须符合标识符的规则。

column

索引所基于的一列或多列。 指定两个或多个列名，可为指定列的组合值创建组合索引。 在 table_or_view_name 后的括号中，按排序优先级列出组合索引中要包括的列。

一个组合索引键中最多可组合 32 列。 组合索引键中的所有列必须在同一个表或视图中。 对于聚集索引，组合索引值允许的最大大小为 900 字节，对于非聚集索引则为 1,700 字节。 对于 SQL 数据库 和 SQL Server 2016 (13.x) 以前的版本，此限制为 16 列和 900 字节。

无法将 ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml 或 image 大型对象 (LOB) 数据类型的列指定为索引的键列。 此外，索引视图定义不能包含 ntext、 文本或 图像 列，即使它们未在语句中 CREATE INDEX 引用。

如果 CLR 用户定义类型支持二进制排序，则可以为该类型的列创建索引。 另外，对于已定义为用户定义类型列的方法调用的计算列，只要这些方法标记为确定性方法且不执行数据访问操作，便可为该计算列创建索引。 有关为 CLR 用户定义类型列编制索引的详细信息，请参阅 CLR 用户定义类型。

[ ASC | DESC ]

确定特定索引列的升序或降序排序方向。 默认值为 ASC。

INCLUDE (column [ ,... n ] )

指定要添加到非聚集索引的叶级别的非键列。 非聚集索引可以唯一，也可以不唯一。

列名不能在 INCLUDE 列表中重复，不能同时用作键列和非键列。 如果对表定义了聚集索引，则非聚集索引始终隐式包含聚集索引列。 有关详细信息，请参阅 创建包含列的索引。

允许除 text、 ntext和 image之外的所有数据类型。 从 Azure SQL 数据库中的 SQL Server 2012（11.x）开始，在 Azure SQL 托管实例中，如果任何一个指定的非键列是 varchar（max）、 nvarchar（max）或 varbinary（max） 数据类型，则可以使用 ONLINE 此选项生成或重新生成索引。

精确或不精确的确定性计算列都可以是包含列。 只要允许计算列数据类型作为包含列，就可以包含从 image、 ntext、 text、 varchar（max）、 nvarchar（max）、 varbinary（max）和 xml 数据类型派生的计算列。 有关详细信息，请参阅 计算列上的索引。

有关创建 XML 索引的信息，请参阅 CREATE XML INDEX。

WHERE <filter_predicate>

通过指定索引中要包含哪些行来创建筛选索引。 筛选索引必须是对表的非聚集索引。 为筛选索引中的数据行创建筛选统计信息。

筛选器谓词使用简单的比较逻辑，不能引用计算列、用户定义的数据类型（UDT）列、空间数据类型列或 hierarchyid 数据类型列。 不允许使用比较运算符与 NULL 文本进行比较。 而改用 IS NULL 和 IS NOT NULL 运算符。

下面是一些 Production.BillOfMaterials 表筛选谓词示例：

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

筛选索引不适用于 XML 索引和全文检索。 对于 UNIQUE 索引，只有所选行必须具有唯一的索引值。 筛选索引不允许有 IGNORE_DUP_KEY 选项。

ON partition_scheme_name ( column_name )

指定分区方案，该方案定义要将已分区索引的分区映射到的文件组。 须通过执行 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME，使数据库中存在该分区方案。 column_name 指定索引的分区列。 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。 column_name 不限于索引定义中的列。 可以指定基表中的任何列，但分区唯一索引时除外， column_name 必须从用作唯一键的列中选择。 通过此限制，数据库引擎可验证单个分区中的键值唯一性。

注意

在对非唯一的聚集索引进行分区时，如果尚未指定分区依据列，则默认情况下数据库引擎将在聚集索引键列表中添加分区依据列。 在对非唯一的非聚集索引进行分区时，如果尚未指定分区依据列，则数据库引擎会添加分区依据列作为索引的非键（包含）列。

如果未指定 partition_scheme_name 或 filegroup 且该表已分区，则索引会与基础表使用相同分区依据列并被放入同一分区方案中。

注意

您不能对 XML 索引指定分区方案。 如果基表已分区，则 XML 索引与该表使用相同的分区方案。

有关分区索引、 已分区表和索引的详细信息。

ON filegroup_name

为指定文件组创建指定索引。 如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。

ON [default]

在表或视图所在的文件组或分区方案上创建指定索引。

default在此上下文中，术语不是关键字。 它是表或视图的文件组或分区方案的标识符，必须以 in 或 in ON "default" 或 .ON [default] 如果 "default" 已指定，则 QUOTED_IDENTIFIER 选项必须为 ON 当前会话。 这是默认设置。 有关详细信息，请参阅 SET QUOTED_IDENTIFIER。

注意

在上下文 CREATE INDEX中， "default"[default] 不指示数据库默认文件组。 它们指示基表或视图使用的文件组或分区方案。 这不同于 CREATE TABLE数据库默认文件组上表的位置 "default" 和 [default] 位置。

[ FILESTREAM_ON { filestream_filegroup_namepartition_scheme_name | "NULL" } ]

在创建聚集索引时，指定表的 FILESTREAM 数据的位置。 FILESTREAM_ON 子句用于将 FILESTREAM 数据移动到不同的 FILESTREAM 文件组或分区方案。

filestream_filegroup_name是 FILESTREAM 文件组的名称。 该文件组须包含一个使用 CREATE DATABASE 或 ALTER DATABASE 语句为该文件组定义的文件；否则，会引发错误。

如果表已分区，则必须包含 FILESTREAM_ON 子句并且必须指定 FILESTREAM 文件组的分区方案，此分区方案需使用与此表分区方案相同的分区功能和分区列。 否则将引发错误。

如果该表未分区，则无法对 FILESTREAM 列分区。 此表的 FILESTREAM 数据必须存储在一个由 FILESTREAM_ON 子句指定的文件组中。

如果正在创建一个聚集索引并且此表不包含 FILESTREAM 列，则可以在 FILESTREAM_ON NULL 语句中指定 CREATE INDEX。

有关详细信息，请参阅 FILESTREAM (SQL Server)。

<object>::=

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

database_name

数据库的名称。

schema_name

表或视图所属架构的名称。

table_or_view_name

要为其建立索引的表或视图的名称。

若要在视图上创建索引，必须使用该视图定义 SCHEMABINDING该视图。 必须先为视图创建唯一的聚集索引，才能为该视图创建非聚集索引。 有关索引视图的详细信息，请参阅 “备注”。

从 SQL Server 2016 (13.x) 开始，该对象可以是聚集列存储索引存储的表。

当 <database_name>.<schema_name>.<object_name> 是当前数据库名称时，Azure SQL 数据库支持由三部分构成的名称 <database_name> 格式，或者 <database_name>tempdb，<object_name> 以 # 或 ##开头。 如果架构名称 dbo，则可以省略 <schema_name>。

<relational_index_option>::=

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

PAD_INDEX = { ON | OFF }

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

• ON

  填充因子指定的可用空间百分比应用于索引的中间级别页。 如果未同时指定 FILLFACTORPAD_INDEX 设置为 ON，则使用 sys.indexes 中的填充因子值。

• OFF

  考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。 如果 PAD_INDEX 设置为 ON 但未指定填充因子，则也会发生这种情况。

仅 PAD_INDEX 当指定此选项时才 FILLFACTOR 有用，因为 PAD_INDEX 使用指定的 FILLFACTOR百分比。 如果指定的 FILLFACTOR 百分比不足以允许一行，则数据库引擎在内部将覆盖该百分比以允许最小值。 中间索引页上的行数从不小于 2，而不管其值有多 FILLFACTOR低。

在后向兼容语法中，WITH PAD_INDEX 等同于 WITH PAD_INDEX = ON。

FILLFACTOR = fillfactor

指定一个百分比，指示在数据库引擎创建或重新生成索引的过程中，应将每个索引页面的叶级填充到什么程度。 填充因子值必须是介于 1 到 100 的整数值。 填充因子的值 0 和 100 在所有方面都是相同的。 如果 fillfactor 为 100，数据库引擎会创建完全填充叶级页的索引。

FILLFACTOR 设置仅在创建或重新生成索引时应用。 数据库引擎并不会在页中动态保持指定的可用空间百分比。

若要查看填充因子设置，请使用 fill_factorsys.indexes 目录视图中的列。

重要

创建 FILLFACTOR 小于 100 的索引会增加数据占用的存储空间量，因为数据库引擎在创建或重新生成索引时会根据填充因子重新分发数据。

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

SORT_IN_TEMPDB = { ON | OFF }

指定是否将临时排序结果存储在 tempdb中。 默认值为OFF“超大规模”Azure SQL 数据库除外。 对于“超大规模”中的所有索引生成作，除非使用可恢复索引生成，否则始终 SORT_IN_TEMPDBON。 对于可恢复索引生成，SORT_IN_TEMPDB 始终 OFF。

• ON

  用于生成索引的中间排序结果存储在其中 tempdb。 这可以减少创建索引所需的时间。 但是，这会增加索引生成期间所使用的磁盘空间量。

• OFF

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

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

在后向兼容语法中，WITH SORT_IN_TEMPDB 等同于 WITH SORT_IN_TEMPDB = ON。

IGNORE_DUP_KEY = { ON | OFF }

指定在插入操作尝试向唯一索引插入重复键值时的错误响应。 IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。 当执行 CREATE INDEX、ALTER INDEX 或 UPDATE 时，该选项无效。 默认值为 OFF。

• ON

  向唯一索引插入重复键值时会出现警告消息。 不会插入违反唯一性约束的行。

• OFF

  向唯一索引插入重复键值时会出现错误消息。 整个 INSERT 语句将回滚。

IGNORE_DUP_KEY 对于在视图、非唯一索引、XML 索引、空间索引和筛选索引上创建的索引，不能设置为 ON < a0/>。

若要查看索引的 IGNORE_DUP_KEY 设置，请使用 ignore_dup_key 目录视图中的 列。

在后向兼容语法中，WITH IGNORE_DUP_KEY 等同于 WITH IGNORE_DUP_KEY = ON。

STATISTICS_NORECOMPUTE = { ON | OFF}

指定是否重新计算统计信息。 默认值为 OFF。

• ON

  不会自动重新计算过时的统计信息。

• OFF

  启用统计信息自动更新功能。

若要还原自动统计信息更新，请将 STATISTICS_NORECOMPUTE 设置为 OFF，或在没有 UPDATE STATISTICS 子句的情况下执行 NORECOMPUTE。

警告

如果通过设置 STATISTICS_NORECOMPUTE = ON禁用统计信息的自动重新计算，可能会阻止查询优化器为涉及表的查询选取最佳执行计划。

将 STATISTICS_NORECOMPUTE 设置为 ON 不会阻止更新在索引重新生成作期间发生的索引统计信息。

在后向兼容语法中，WITH STATISTICS_NORECOMPUTE 等同于 WITH STATISTICS_NORECOMPUTE = ON。

STATISTICS_INCREMENTAL = { ON | OFF }

适用于：SQL Server 2014（12.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

创建统计信息时 ON，按分区统计信息创建。 删除统计信息树时 OFF，SQL Server 会重新计算统计信息。 默认值为 OFF。

如果不支持每个分区统计信息，将忽略该选项并生成警告。 在以下情况下不支持增量统计信息：

• 使用未与基表的分区对齐的索引创建的统计信息。
• 对 Always On 可读辅助数据库创建的统计信息。
• 对只读数据库创建的统计信息。
• 对筛选的索引创建的统计信息。
• 对视图创建的统计信息。
• 对内部表创建的统计信息。
• 使用空间索引或 XML 索引创建的统计信息。

DROP_EXISTING = { ON | OFF }

一个选项，用于删除并重新生成具有已修改列规范的现有聚集或非聚集索引，同时为该索引设置相同的名称。 默认值为 OFF。

• ON

  指定删除并重新生成现有索引，该索引必须与 index_name 参数具有相同名称。

• OFF

  指定不删除和重新生成现有索引。 如果指定的索引名称已存在，SQL Server 将显示错误。

使用 DROP_EXISTING，可更改以下内容：

• 将非聚集行存储索引更改为聚集行存储索引。

使用 DROP_EXISTING 时，无法更改：

• 将聚集行存储索引更改为非聚集行存储索引。
• 将聚集列存储索引更改为任何类型的行存储索引。

在后向兼容语法中，WITH DROP_EXISTING 等同于 WITH DROP_EXISTING = ON。

ONLINE = { ON | OFF }

指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认值为 OFF。

重要

在 Microsoft SQL Server 的各版本中均不提供联机索引操作。 有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

• ON

  在索引操作期间不持有长期表锁。 在索引作的主阶段，源表上只保留意向共享锁（IS）。 这使得能够继续对基础表和索引进行查询或更新。 在作开始时，在源对象上保留一个共享锁（S）在短时间内。 在作结束时，如果创建了非聚集索引，则会在对象上获取共享锁（S）锁。 当创建或删除聚集索引以及重新生成聚集索引或非聚集索引时，将获取架构修改 （Sch-M） 锁。 ONLINE 在本地临时表上创建索引时，无法设置为 ON 该索引。

  注意

  可以使用 WAIT_AT_LOW_PRIORITY 选项来减少或避免在联机索引作期间阻止。 有关详细信息，请参阅 联机索引作WAIT_AT_LOW_PRIORITY。

• OFF

  在索引操作期间应用表锁。 创建、重新生成或删除聚集、空间或 XML 索引的脱机索引作，或者重新生成或删除非聚集索引，获取表上的架构修改 （Sch-M） 锁。 这样可以防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引作最初获取表上的共享锁（S） 锁。 这可以防止修改基础表定义，但允许在索引生成正在进行时读取和修改表中的数据。

有关详细信息，请参阅 联机执行索引作 和 联机索引作指南。

可以联机创建索引（包括全局临时表中的索引），但以下情况除外：

• XML 索引
• 对本地临时表的索引
• 视图的初始唯一聚集索引
• 已禁用的聚集索引
• SQL Server 2017（14.x）和更早版本中的聚集列存储索引
• SQL Server 2016（13.x）和旧版本中的非聚集列存储索引
• 聚集索引，前提是基础表包含 LOB 数据类型（image、ntext、text）和空间数据类型
• varchar(max) 和 varbinary(max) 列不能是索引键的一部分。 在 SQL Server（从 SQL Server 2012 （11.x）开始，在 Azure SQL 数据库和 Azure SQL 托管实例中，当表包含 varchar（max） 或 varbinary（max） 列时，可以使用此选项生成或重新生成 ONLINE 包含其他列的聚集索引。
• 具有聚集列存储索引的表上的非聚集索引

有关详细信息，请参阅 联机索引作的工作原理。

RESUMABLE = { ON | OFF }

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

指定联机索引操作是否可恢复。 有关详细信息，请参阅 可恢复索引作 和 可恢复索引注意事项。

• ON

  索引操作可恢复。

• OFF

  索引操作不可恢复。

将 MAX_DURATION = time [MINUTES] 与 一起使用（要求 RESUMABLE = ON）

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

指定在暂停前执行可恢复索引作的时间（以分钟为单位）。

ALLOW_ROW_LOCKS = { ON | OFF }

指定是否允许行锁。 默认值为 ON。

• ON

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

• OFF

  不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

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

• ON

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

• OFF

  不使用页锁。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

指定是否要进行优化以避免最后一页插入争用。 默认值为 OFF。 有关详细信息，请参阅 “顺序键 ”部分。

MAXDOP = max_degree_of_parallelism

替代索引作的 最大并行度 配置选项。 有关详细信息，请参阅 配置 max degree of parallelism 服务器配置选项。 使用 MAXDOP 来限制索引生成作的并行度和生成的资源消耗。

max_degree_of_parallelism 可以是：

• 1

  取消生成并行计划。

• >1

  根据当前系统工作负荷，将并行索引作中使用的最大并行度限制为指定数目或更少。

• 0（默认值）

  使用在服务器、数据库或工作负荷组级别指定的并行度，除非根据当前系统工作负荷减少。

有关详细信息，请参阅 配置并行索引作。

注意

并非在 Microsoft SQL Server 的每个版本中均提供并行索引操作。 有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

DATA_COMPRESSION

为指定的索引、分区号或分区范围指定数据压缩选项。 选项如下：

• 无

  不压缩索引或指定的分区。 这不适用于列存储索引。

• ROW

  使用行压缩来压缩索引或指定的分区。 这不适用于列存储索引。

• PAGE

  使用页压缩来压缩索引或指定的分区。 这不适用于列存储索引。

• COLUMNSTORE

  适用于：SQL Server 2014（12.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

  仅适用于列存储索引，包括非聚集列存储索引和聚集列存储索引。

• COLUMNSTORE_ARCHIVE

  适用于：SQL Server 2014（12.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

  仅适用于列存储索引，包括非聚集列存储索引和聚集列存储索引。 COLUMNSTORE_ARCHIVE 进一步将指定的分区压缩为较小的大小。 这可用于存档，或者用于要求更小存储大小并且可以付出更多时间来进行存储和检索的其他情形。

有关压缩的详细信息，请参阅数据压缩。

XML_COMPRESSION

适用于：SQL Server 2022 （16.x） 及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

为包含一个或多个 xml 数据类型列的指定索引指定 XML 压缩选项。 选项如下：

• ON

  使用 XML 压缩来压缩索引或指定的分区。

• OFF

  索引或指定的分区不使用 XML 压缩进行压缩。

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

指定应用 DATA_COMPRESSION 或 XML_COMPRESSION 设置的分区。 如果未对索引进行分区，则 ON PARTITIONS 参数将生成错误。 如果未提供 ON PARTITIONS 子句，DATA_COMPRESSION 或 XML_COMPRESSION 选项将应用于已分区索引的所有分区。

可以通过下列方式指定 <partition_number_expression>：

• 提供分区号（例如：ON PARTITIONS (2)）。
• 提供多个单独分区的分区号，并用逗号分隔（例如：ON PARTITIONS (1, 5)）。
• 同时提供范围和单独分区，例如：ON PARTITIONS (2, 4, 6 TO 8)。

<range>可以指定为用关键字TO分隔的分区号，例如： ON PARTITIONS (6 TO 8)

若要为不同分区设置不同的数据压缩类型，请多次指定 DATA_COMPRESSION 选项，例如：

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

还可以多次指定 XML_COMPRESSION 选项，例如：

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

备注

为 CREATE INDEX 语句创建查询计划时，查询优化器可以选择扫描另一个索引，而不是执行表扫描。 在某些情况下，可能会消除排序作。 在多处理器计算机上， CREATE INDEX 可以使用与创建索引关联的扫描和排序作的并行度，就像其他查询一样。 有关详细信息，请参阅 配置并行索引作。

CREATE INDEX如果数据库恢复模式设置为大容量记录或简单，则作可能最小记录。

可以为临时表创建索引。 删除表或超出范围时，将删除索引。

添加主键约束时，聚集索引基于表变量生成。 同样，添加唯一约束时，非聚集索引在表变量上生成。 当表变量超出范围时，将删除索引。

索引支持扩展属性。

CREATE INDEX 在 Microsoft Fabric 中不受支持。

聚集索引

对表（堆）创建聚集索引或删除和重新创建现有聚集索引时，要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。 有关聚集索引的详细信息，请参阅 创建聚集索引 和 SQL Server 索引体系结构和设计指南。

“非聚集索引”

从 Azure SQL 数据库和 Azure SQL 托管实例中的 SQL Server 2016（13.x）开始，可以在存储为聚集列存储索引的表上创建非聚集索引。 如果首先在存储为堆或聚集索引的表上创建非聚集索引，则以后将该表转换为聚集列存储索引时，该索引会保留。 重新生成聚集列存储索引也不需要删除非聚集索引。

为作为聚集列存储索引进行存储的表创建非聚集索引时，FILESTREAM_ON 选项无效。

唯一索引

当存在唯一索引时，数据库引擎会在每次添加或修改数据时检查重复值。 将回滚生成重复键值的作，数据库引擎将返回错误消息。 即使数据添加或修改作更改了许多行，但只导致一个重复行，也是如此。 如果在选项设置为ON唯一索引IGNORE_DUP_KEY的情况下尝试插入行，则忽略违反唯一索引的行。

已分区索引

创建和维护分区索引的方式与已分区表相同，但与普通索引一样，将分区索引作为单独数据库对象来进行处理。 可以在未分区的表中使用分区索引，也可以在已分区表中使用未分区索引。

如果要对已分区表创建索引，并且不指定用于放置该索引的文件组，则会按照与基础表相同的方式为该索引分区。 这是因为在默认情况下，索引与其基础表放在同一文件组中，并且对应使用相同分区依据列的相同分区方案中的已分区表。 当索引与表使用同一个分区方案和分区列时，索引将与表对齐。

警告

对超过 1,000 个分区的表创建和重新生成非对齐索引是可能的，但不支持。 这样做可能会导致性能下降，或在执行这些操作的过程中占用过多内存。 建议仅在分区数超过 1,000 时使用对齐索引。

在对非唯一的聚集索引分区时，如果尚未指定分区依据列，则默认情况下数据库引擎将在聚集索引键列表中添加任意分区依据列。

可以使用与为表创建索引时相同的方式，为已分区表创建索引视图。 有关已分区索引的详细信息，请参阅 分区表和索引 以及 SQL Server 索引体系结构和设计指南。

创建或重新生成索引时，查询将优化索引上的统计信息。 对于分区索引，查询优化器使用默认采样算法，而不是扫描表中的所有行以获取非分区索引。 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息，请使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。

筛选索引

筛选索引是一种经过优化的非聚集索引，适用于从表中选择少数行的查询。 筛选索引使用筛选谓词对表中的部分数据进行索引。 设计良好的筛选索引可以提高查询性能，降低存储成本和维护成本。

筛选索引所需的 SET 选项

SET每当出现以下任一情况时，都需要“必需值”列中的选项：

• 创建筛选索引。

• 、INSERTUPDATE、DELETE或MERGE语句修改筛选索引中的数据。

• 查询优化器使用该筛选索引生成查询计划。

  SET 选择	所需的值	默认服务器值	默认 OLE DB 和 ODBC 值	默认 DB-Library 值
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  ¹ 设置为ANSI_WARNINGSON在ARITHABORTON数据库兼容级别设置为 90 或更高版本时隐式设置。 如果数据库兼容性级别设置为 80 或更早版本，则必须 ARITHABORT 将选项显式设置为 ON。

SET如果选项不正确，可能会出现以下情况：

• 创建筛选的索引失败。
• 数据库引擎生成错误，并回滚INSERT更改索引中的数据的 、UPDATEDELETE或MERGE语句。
• 查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。

有关筛选索引的详细信息，请参阅 创建筛选索引 和 SQL Server 索引体系结构和设计指南。

空间索引

有关空间索引的信息，请参阅 CREATE SPATIAL INDEX 和 空间索引概述。

XML 索引

有关 XML 索引的信息，请参阅 CREATE XML INDEX 和 XML 索引 (SQL Server)。

索引键大小

对于聚集索引，索引键的最大大小为 900 字节，对于非聚集索引为 1700 字节。 （在 SQL 数据库和 SQL Server 2016 (13.x) 之前，限制始终为 900 字节。） 如果列中的现有数据在创建索引时不超过限制，则可以创建超出字节限制的 varchar 列上的索引;但是，对导致总大小大于限制的列的后续插入或更新作失败。 聚集索引的索引键不能包含具有分配单元中ROW_OVERFLOW_DATA现有数据的 varchar 列。 如果在 varchar 列上创建了聚集索引，并且现有数据位于分配单元中 IN_ROW_DATA ，则对推送数据行外列的后续插入或更新作将失败。

非聚集索引可以在索引的叶级别中包含非键（包含）列。 计算索引键大小时，数据库引擎不会考虑这些列。 有关详细信息，请参阅 创建包含列的索引 以及 SQL Server 索引体系结构和设计指南。

注意

在对表进行分区时，如果分区键列尚未出现在非唯一聚集索引中时，它们将由数据库引擎添加到索引中。 索引列的合并后的大小（不将包含列计算在内）加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节。

计算列

可以对计算列创建索引。 此外，计算列可以具有该属性 PERSISTED。 这意味着 数据库引擎 在表中存储计算值，并且在计算列所依赖的任何其他列发生更新时更新这些值。 如果 数据库引擎 对列创建了索引并且该索引由某查询引用，则会使用这些持久值。

若要对计算列建立索引，则该计算列必须具有确定性并精确。 但是，使用 PERSISTED 属性可扩展可索引计算列的类型以包括：

• 基于 Transact-SQL 和 CLR 函数以及由用户标记为确定性的 CLR 用户定义类型方法的计算列。
• 基于数据库引擎定义为确定性但不精确的表达式的计算列。

持久化计算列要求设置以下 SET 选项，如上一部分 筛选索引的必需 SET 选项所示。

PRIMARY KEY只要计算列满足索引的所有条件，该UNIQUE列或约束就可以包含计算列。 具体来说，计算列必须具有确定性并精确，或者具有确定性并持久化。 有关确定性的详细信息，请参阅确定性函数和不确定性函数。

只要计算列的数据类型可以作为索引键列或非键列，从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列（作为键列或包含非键列）上就可以创建索引 。 例如，不能对 xml 计算列创建主 XML 索引。 如果索引键大小超过 900 字节，会显示一条警告消息。

在计算列上创建索引可能会导致以前运行的插入或更新作失败。 当计算列导致算术错误时，可能会发生此类失败。

例如，在下表中，尽管计算列 c 的表达式在插入行时会导致算术错误，但 INSERT 该语句有效。

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

但是，如果在计算列 c上创建索引，则同一 INSERT 语句将失败。

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

有关详细信息，请参阅 计算列上的索引。

索引中的包含列

可以将非键列（称为包含列）添加到非聚集索引的叶级别，从而通过涵盖查询来提高查询性能。 也就是说，查询中引用的所有列都作为键列或非键列包含在索引中。 这允许查询优化器从非聚集索引扫描或查找获取所有必需信息;无法访问表或聚集索引数据。 有关详细信息，请参阅 创建包含列的索引 以及 SQL Server 索引体系结构和设计指南。

指定索引选项

SQL Server 2005 （9.x） 引入了新的索引选项，并修改了指定选项的方式。 在向后兼容的语法中， WITH option_name 等效于 WITH (option_name = ON). 在设置索引选项时，下列规则适用：

• 只能使用 WITH (<option_name> = <ON | OFF>) 指定新的索引选项。
• 指定选项时不能在同一语句中同时使用向后兼容语法和新语法。 例如，指定 WITH (DROP_EXISTING, ONLINE = ON) 会导致语句失败。
• 在创建 XML 索引时，必须使用 WITH (<option_name> = <ON | OFF>) 指定选项。

DROP_EXISTING 子句

可使用 DROP_EXISTING 子句重新生成索引、添加或删除列、修改选项、修改列排序顺序或更改分区方案或文件组。

如果索引强制实施 PRIMARY KEY 或 UNIQUE 约束，并且不会以任何方式更改索引定义，则会删除索引并重新创建以保留现有约束。 不过，如果索引定义已改变，则该语句将失败。 若要更改或UNIQUE约束的定义PRIMARY KEY，请删除该约束，并使用新定义添加约束。

为已经具有非聚集索引的表重建聚集索引时（使用相同或不同的键集），DROP_EXISTING 可以提高性能。 DROP_EXISTING 代替先对旧的聚集索引执行 DROP INDEX 语句，然后再对新的聚集索引执行 CREATE INDEX 语句的过程。 而是将重新生成一次非聚集索引，之后仅在索引定义已更改时再重新生成。 如果索引定义与原始索引具有相同的索引名称、键列和分区列、唯一性属性以及排序顺序，则 DROP_EXISTING 子句不会重新生成非聚集索引。

无论是否重新生成非聚集索引，它们都将始终保留在其原始文件组或分区方案中，并使用原始的分区函数。 如果聚集索引被重新生成到其他文件组或分区方案中，这些非聚集索引不会通过移动来与聚集索引的新位置保持一致。 因此，即使以前与聚集索引对齐的非聚集索引，它们可能不再与其对齐。 有关分区索引对齐的详细信息，请参阅 已分区表和索引。

DROP_EXISTING如果相同的索引键列按相同的顺序和相同的升序或降序使用，则子句不会再次对数据进行排序，除非索引语句指定了非聚集索引，并且选项ONLINE设置为 OFF。 如果禁用聚集索引，则必须使用设置为 > 执行该作。 如果禁用了非聚集索引且未与禁用的聚集索引关联， CREATE INDEX WITH DROP_EXISTING 则可以使用 ONLINE 设置为 OFF 或设置为或 ON执行该作。

注意

删除或重新生成具有 128 个或更多区数的索引时，数据库引擎会将实际页释放及其关联的锁推迟到事务提交后。 有关详细信息，请参阅 延迟解除分配。

ONLINE 选项

下列指南适用于联机执行索引操作：

• 不能在执行联机索引操作的过程中更改、截断或删除基础表。
• 索引操作期间需要额外的临时磁盘空间。
• 可以对分区索引以及包含持久性计算列或包含列的索引执行联机操作。
• 通过 WAIT_AT_LOW_PRIORITY 参数选项，可以决定索引作在等待 Sch-M 锁时如何继续。 有关详细信息，请参阅 WAIT_AT_LOW_PRIORITY

有关详细信息，请参阅 联机执行索引操作。

可恢复索引操作

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

可以使联机索引创建作可恢复。 这意味着索引生成可以停止，稍后从停止的点重新启动。 若要以可恢复方式运行索引生成，请指定 RESUMABLE = ON 该选项。

以下准则适用于可恢复索引作：

• 若要使用 RESUMABLE 选项，还必须使用 ONLINE 选项。
• RESUMABLE 选项不会保留在给定索引的元数据中，仅适用于当前 DDL 语句的持续时间。 因此，必须显式指定 RESUMABLE = ON 子句才能启用可恢复性。
• 可以在两个上下文中指定 MAX_DURATION 选项：
  • MAX_DURATION 选项 RESUMABLE 指定重新生成索引的时间间隔。 此时间过后，如果索引重新生成仍在运行，则会暂停。 决定何时可以恢复暂停索引的重新生成。 的 MAX_DURATION 必须大于 0 分钟且小于或等于一周（7 * 24 * 60 = 10080 分钟）。 索引作中的长时间暂停可能会显著影响特定表的 DML 性能以及数据库磁盘容量，因为原始索引和新创建的索引都需要磁盘空间，并且需要 DML作更新。 如果省略 MAX_DURATION 选项，索引作将一直持续到完成或失败为止。
  • MAX_DURATION 选项的 WAIT_AT_LOW_PRIORITY 指定在执行作之前使用低优先级锁等待的时间。 有关详细信息，请参阅 联机索引作WAIT_AT_LOW_PRIORITY。
• 若要立即暂停索引作，可以执行 ALTER INDEX PAUSE 命令，或执行 KILL <session_id> 命令。
• 使用相同的参数重新执行原始 CREATE INDEX 语句会恢复暂停的索引生成作。 还可以通过执行 ALTER INDEX RESUME 语句来恢复暂停的索引生成作。
• ABORT 命令会终止运行索引生成的会话，并取消索引作。 无法恢复已中止的索引作。

可恢复索引作在完成、暂停或失败之前运行。 如果作暂停，则会发出一个错误，指示作已暂停，并且索引创建未完成。 如果作失败，也会发出错误。

若要查看索引作是否作为可恢复作执行并检查其当前执行状态，请使用 sys.index_resumable_operations 目录视图。

资源

可恢复索引作需要以下资源：

• 保留要生成的索引所需的额外空间，包括暂停生成的时间。
• 排序阶段中的额外日志吞吐量。 与常规联机索引创建相比，可恢复索引的总体日志空间使用率较低，并允许在此操作期间进行日志截断。
• 不允许暂停索引作时尝试修改与所创建的索引关联的表的 DDL 语句。
• 在操作期间暂停时和运行操作时都会阻止对内置索引进行虚影清除。
• 如果表包含 LOB 列，则可恢复的聚集索引生成需要在作开始时锁定架构修改（Sch-M）。

当前功能限制

可恢复索引创建作具有以下限制：

• 恢复联机索引创建作暂停后，无法更改初始值 MAXDOP 。
• 可恢复索引作不支持 SORT_IN_TEMPDB = ON 选项。
• 不能在显式事务内执行具有 RESUMABLE = ON 的 DDL 命令。
• 无法创建包含：
  • 计算列或 timestamp （rowversion） 列作为键列。
  • LOB 列作为包含的列。
• 不支持恢复索引作：
  • ALTER INDEX REBUILD ALL 命令
  • ALTER TABLE REBUILD 命令
  • 列存储索引
  • 筛选索引
  • 禁用的索引

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

适用于：SQL Server 2022 （16.x） 及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

如果不使用该 WAIT_AT_LOW_PRIORITY 选项，必须完成对表或索引持有锁的活动阻塞事务，才能启动和完成索引创建作。 当联机索引作启动并在它完成之前，它需要获取共享的（S）或架构修改（Sch-M）锁，并暂时保留它。 尽管锁只保留短时间，但它可能会显著影响工作负荷吞吐量、增加查询延迟或导致执行超时。

为了避免这些问题，WAIT_AT_LOW_PRIORITY 选项允许你管理联机索引作启动和完成所需的 S 或 Sch-M 锁的行为，从三个选项中进行选择。 在所有情况下，如果在由 MAX_DURATION = n [minutes] 指定的等待时间期间没有涉及索引作的阻塞，索引作将立即继续。

WAIT_AT_LOW_PRIORITY 使联机索引作使用低优先级锁等待，从而允许使用正常优先级锁的其他作同时继续。 省略 WAIT_AT_LOW_PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

MAX_DURATION = 时间 [MINUTES]

联机索引作使用低优先级锁等待的等待时间（以分钟为单位指定的整数值）。 如果操作在一段时间内被 MAX_DURATION 阻止，则会执行指定的 ABORT_AFTER_WAIT 操作。 MAX_DURATION 时间始终以分钟为单位，可以省略单词 MINUTES 。

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE：继续等待具有正常优先级的锁。
• SELF：退出当前正在执行的联机索引作，而不采取任何作。 SELF 为 0 时，无法使用选项 MAX_DURATION。
• BLOCKERS：终止阻止联机索引作的所有用户事务，以便该作可以继续。 BLOCKERS 选项要求执行 CREATE INDEX 或 ALTER INDEX 语句的主体具有 ALTER ANY CONNECTION 权限。

可以使用以下扩展事件来监视等待低优先级锁定的索引作：

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

行锁和页锁选项

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，在访问索引时允许使用行级别、页级别和表级别锁定。 数据库引擎将选择相应的锁，并且可以将锁从行锁或页锁升级到表锁。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，在访问索引时仅允许使用表级别锁定。

警告

不建议在索引上禁用行锁或页锁。 可能会出现与并发相关的问题，并且某些功能可能不可用。 例如，当 ALLOW_PAGE_LOCKS 设置为 OFF时，无法重新组织索引。

顺序键

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例。

最后一页插入争用是在以下情况下发生的常见性能问题：当大量并发线程尝试将行插入包含顺序键的索引时。 如果前导键列包含始终增加（或减少）的值（如标识列），或包含默认为当前日期/时间的日期，索引就会被视为顺序索引。 由于插入的键是连续的，因此所有新行都插入到索引结构的末尾，换句话说，在同一页上插入。 这会导致内存中页的争用，这些争用可以观察到为等待获取有关页面的闩锁的多个线程。 相应的等待类型为 PAGELATCH_EX。

通过启用 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引选项，可以在数据库引擎内启用优化，有助于提高索引中高并发插入的吞吐量。 它适用于因包含顺序键而容易发生最后一页插入争用的索引，但可能也有助于在 B 树索引结构的其他区域中有作用点的索引。

注意

文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，数据库引擎实现了 B+ 树。 这不适用于列存储索引或内存优化表上的索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

数据压缩

有关数据压缩的详细信息，请参阅 数据压缩。

以下是在使用数据压缩时在索引生成作上下文中要考虑的要点：

• 通过压缩可将更多的行存储在页上，但不能更改最大行大小。
• 对索引的非叶页不会进行页压缩，但可进行行压缩。
• 每个非聚集索引都有单独的压缩设置，并且不会继承基础表的压缩设置。
• 对堆创建聚集索引时，聚集索引会继承该堆的压缩状态，除非指定了另一压缩状态。

若要评估更改压缩状态如何影响表、索引或分区的空间使用情况，请使用 sp_estimate_data_compression_savings 存储过程。

XML 压缩

适用于：SQL Server 2022 (16.x) 及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

许多数据压缩注意事项适用于 XML 压缩。 还应该注意以下事项：

• 指定分区列表时，可以在单个分区上启用 XML 压缩。 如果未指定分区列表，则所有分区都设置为启用 XML 压缩。 创建表或索引时，除非另行指定，否则将禁用 XML 数据压缩。 修改表时，除非指定了其他压缩设置，否则将保留现有压缩设置。
• 如果指定分区列表或分区超出范围，将生成错误。
• 对堆创建聚集索引时，聚集索引会继承该堆的 XML 压缩状态，除非指定了另一压缩选项。
• 若要更改堆的 XML 压缩设置，要求对表重新生成所有非聚集索引，以便它们具有指向堆中的新行位置的指针。
• 可以联机或脱机启用或禁用 XML 压缩功能。 当执行联机操作时，对堆启用压缩功能是单线程的。
• 若要确定分区表中分区的 XML 压缩状态，请使用 xml_compression 目录视图的 sys.partitions 列。

索引统计信息

创建行存储索引时，数据库引擎还会针对索引的键列创建 统计信息 。 sys.stats 目录视图中统计信息对象的名称与索引的名称匹配。 对于非分区索引，统计信息是使用完全扫描数据生成的。 对于分区索引，统计信息是使用默认采样算法生成的。

创建列存储索引时，数据库引擎也会在 sys.stats 中创建统计信息对象。 此统计信息对象不包含统计信息数据，如直方图和密度向量。 通过编写数据库脚本创建数据库克隆时，会使用它。 此时， DBCC SHOW_STATISTICS 这些和 UPDATE STATISTICS ... WITH STATS_STREAM 命令用于获取列存储元数据（如段、字典和增量存储大小），并将其添加到列存储索引的统计信息中。 此元数据是在常规数据库的查询编译时动态获取的，但由数据库克隆的统计信息对象提供。 任何其他方案中列存储索引上的统计信息对象都不支持 UPDATE STATISTICS 命令。

权限

需要对 ALTER 固定数据库角色中的 db_ddladmin 表或视图或成员身份具有权限。

限制和局限

在 Azure Synapse Analytics和Analytics Platform System (PDW) 中不能执行以下创建：

• 当列存储索引已存在时，不能为数据仓库表创建聚集或非聚集行存储索引。 此行为与 SMP SQL Server 的行为不同，后者允许同一表中同时存在行存储和列存储索引。
• 不能对视图创建索引。

元数据

若要查看现有索引的信息，可以查询 sys.indexes 目录视图。

版本说明

• Azure SQL 数据库不支持除 PRIMARY以外的文件组。
• Azure SQL 数据库和 Azure SQL 托管实例不支持 FILESTREAM 选项。
• 列存储索引在 SQL Server 2012（11.x）之前不可用。
• 从 SQL Server 2017（14.x）、Azure SQL 数据库和 Azure SQL 托管实例中开始，可以使用可恢复索引作。

示例：所有版本。 使用 AdventureWorks 数据库

A. 创建简单的非聚集行存储索引

以下示例为 VendorID 表的 Purchasing.ProductVendor 列创建非聚集索引。

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. 创建简单的非聚集行存储组合索引

以下示例为 SalesQuota 表的 SalesYTD 和 Sales.SalesPerson 列创建非聚集组合索引。

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. 为其他数据库中的表创建索引

以下示例为 VendorID 数据库中 ProductVendor 表的 Purchasing 列创建聚集索引。

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. 将列添加到索引

以下示例在 dbo.FactFinance 表中创建具有两列的 IX_FF 索引。 下一个语句重新生成具有三列的索引并保留现有名称。

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

示例：SQL Server、Azure SQL 数据库

E. 创建唯一非聚集索引

以下示例为 Name 数据库中 Production.UnitMeasure 表的 AdventureWorks2022 列创建唯一非聚集索引。 该索引将强制插入 Name 列中的数据具有唯一性。

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

以下查询通过尝试插入与现有行包含相同值的一行来测试唯一性约束。

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

生成如下错误消息：

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. 使用 IGNORE_DUP_KEY 选项

以下示例首先在该选项设置为 IGNORE_DUP_KEY 时在临时表中插入多行，然后在该选项设置为 ON 时执行相同操作，以演示 OFF 选项的影响。 单个行被插入 #Test 表，在执行第二个多行 INSERT 语句时将导致出现重复值。 表中的行计数会返回插入的行数。

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

请注意，从 Production.UnitMeasure 表中插入的、不违反唯一性约束的行将成功插入。 会发出警告并忽略重复行，但不会回滚整个事务。

将再次执行相同语句，但将 IGNORE_DUP_KEY 设置为 OFF。

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

请注意，即使 Production.UnitMeasure 表中只有一行违反 UNIQUE 索引约束，也不会将其中任何一行插入该表。

G. 使用 DROP_EXISTING 删除和重新创建索引

以下示例使用 ProductID 选项在 Production.WorkOrder 数据库的 AdventureWorks2022 表的 DROP_EXISTING 列上删除并重新创建现有索引。 还设置了 FILLFACTOR 和 PAD_INDEX 选项。

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. 为视图创建索引

以下示例将创建一个视图并为该视图创建索引。 包含两个使用该索引视图的查询。

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. 创建具有包含列（非键列）的索引

以下示例创建具有一个键列 (PostalCode) 和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的非聚集索引。 然后执行该索引覆盖的查询。 若要显示查询优化器选择的索引，执行查询前，请在 SQL Server Management Studio 中的“查询”菜单上选择“显示实际执行计划” 。

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. 创建已分区索引

以下示例为 TransactionsPS1 数据库中现有分区方案 AdventureWorks2022 创建非聚集分区索引。 此示例假定安装了分区索引示例。

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. 创建筛选索引

下面的示例将对 AdventureWorks2022 数据库中的 Production.BillOfMaterials 表创建筛选索引。 筛选谓词可包含那些不是筛选索引中的键列的列。 本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. 创建压缩索引

下面的示例将使用行压缩对无分区表创建索引。

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

下面的示例将通过对索引的所有分区使用行压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

下面的示例将通过对索引的分区 1 使用页压缩并对索引的分区 2 至 4 使用行压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. 使用 XML 压缩创建索引

适用于：SQL Server 2022 (16.x) 及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

下面的示例将使用 XML 压缩对无分区表创建索引。 索引中至少有一列必须是 xml 数据类型。

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

下面的示例将通过对索引的所有分区使用 XML 压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. 创建、恢复、暂停和中止可恢复索引操作

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. 具有不同低优先级锁选项的 CREATE INDEX

下面的示例使用 WAIT_AT_LOW_PRIORITY 选项来指定用于处理阻塞的不同策略。

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

下面的示例使用 RESUMABLE 选项并指定两个 MAX_DURATION 值，第一个应用于 ABORT_AFTER_WAIT 选项，第二个应用于 RESUMABLE 选项。

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

示例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

P. 基本语法

创建、恢复、暂停和中止可恢复索引操作

适用于：SQL Server 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. 为当前数据库中的表创建非聚集索引

以下示例为 VendorID 表的 ProductVendor 列创建非聚集索引。

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. 为其他数据库中的表创建聚集索引

以下示例为 VendorID 数据库中 ProductVendor 表的 Purchasing 列创建非聚集索引。

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. 在表上创建有序的聚集索引

下面的示例在 c1 数据库的 c2 表上的 T1 和 MyDB 列创建有序的聚集索引。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. 在表上将 CCI 转换为有序的聚集索引

下面的示例将现有的聚集列存储索引转换为有序聚集列存储索引，该索引名为 MyOrderedCCI，位于 c1 数据库的 c2 表上的 T2 和 MyDB 列。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

相关内容

• SQL Server 索引体系结构和设计指南
• 联机执行索引操作
• 索引和 ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• 数据类型
• DBCC SHOW_STATISTICS
• DROP INDEX
• XML 索引 (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA