通过

ALTER TABLE index_option (Transact-SQL)

  • 项目

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

指定一组可应用于某个索引的选项，该索引是使用 ALTER TABLE 创建的约束定义的一部分。

有关索引选项的完整说明，请参阅 CREATE INDEX。

Transact-SQL 语法约定

语法

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

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

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

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

注意

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

参数

PAD_INDEX = { ON | OFF }

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

  • ON

    FILLFACTOR 指定的可用空间百分比应用于索引的中间级别页面。

  • OFF 或未指定 fillfactor

    考虑到中间级页上的键集，可以将中间级页几乎填满，但至少要为最大索引行留出足够空间。

FILLFACTOR = fillfactor

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

注意

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

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，请使用 sys.indexes。

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

STATISTICS_NORECOMPUTE = { ON | OFF }

为与指定索引相关的统计信息禁用或启用自动统计信息更新选项 AUTO_STATISTICS_UPDATE。 默认为 OFF。

  • ON

    重新生成索引后，将禁用自动统计信息更新。

  • OFF

    重新生成索引后，将启用自动统计信息更新。

若要还原自动统计信息更新，请STATISTICS_NORECOMPUTEOFF将其设置为或不使用NORECOMPUTE子句执行UPDATE STATISTICS。

警告

如果禁用统计信息自动更新，它可能会阻止查询优化器为涉及表的查询选取最佳执行计划。 应谨慎使用此选项，并且仅由限定的数据库管理员使用。

在重新生成操作期间，此设置不会阻止使用索引相关统计信息的完全扫描自动更新。

ALLOW_ROW_LOCKS = { ON | OFF }

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

  • ON

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

  • OFF

    不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

  • ON

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

  • OFF

    不使用页锁。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

适用于：SQL Server 2019 (15.x) 及更高版本

指定是否针对最后一页插入争用进行优化。 默认为 OFF。 有关详细信息，请参阅文章的“ 顺序键 ” CREATE INDEX 部分。

SORT_IN_TEMPDB = { ON | OFF }

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

  • ON

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

  • OFF

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

ONLINE = { ON | OFF }

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

注意

无法联机创建唯一的非聚集索引。 这包括由于约束UNIQUEPRIMARY KEY而创建的索引。

  • ON

    长期表锁在索引操作期间不保留。 在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。 这使得能够继续对基础表和索引进行查询或更新。 在操作开始时，在源对象上保留一个共享锁（S）在短时间内。 在操作结束时，如果在创建非聚集索引时，会在源上获取一个 S（共享）锁;当创建或删除聚集索引以及重新生成聚集索引或非聚集索引时，将获取 Sch-M （架构修改）锁。 但联机索引锁是短的元数据锁，特别是 Sch-M 锁必须等待此表上的所有阻塞事务完成。 在等待期间，Sch-M 锁在访问同一表时阻止在此锁后等待的所有其他事务。 ONLINE 在本地临时表上创建索引时，无法设置为 ON 该索引。

    注意

    联机索引重新生成可以设置本节稍后介绍的 low_priority_lock_wait 选项。 在联机索引重新生成期间，low_priority_lock_wait 管理 S 和 Sch-M 锁优先级。

  • OFF

    在索引操作期间应用表锁。 这样可防止所有用户在操作期间访问基础表。 创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。 这样可防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。 这会阻止更新基础表，但允许读取操作，例如 SELECT 语句。

有关详细信息，请参阅联机索引操作的工作方式。

注意

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

RESUMABLE = { ON | OFF}

适用于：SQL Server 2022 (16.x) 及更高版本

指定 ALTER TABLE ADD CONSTRAINT 操作是否可恢复。 添加表约束操作在 ON 时是可恢复的。 添加表约束操作在 OFF 时是可恢复的。 默认值为 OFF。 RESUMABLE 选项设置为 ON 时，需要 ONLINE = ON 选项。

MAX_DURATION与 （requiresONLINE = ON） 一起使用RESUMABLE = ON时，指示在暂停之前执行可恢复联机添加约束操作的时间（以分钟为单位指定的整数值）。 如果未指定，则操作会一直持续到完成。 MAXDOP 也支持 RESUMABLE = ON < a0/>。

有关启用和使用可恢复 ALTER TABLE ADD CONSTRAINT 操作的详细信息，请参阅可恢复添加表约束。

MAXDOP = max_degree_of_parallelism

适用于：SQL Server 2008 (10.0.x) 及更高版本

在索引操作期间替代 max degree of parallelism 配置选项。 有关详细信息，请参阅配置最大并行度（服务器配置选项）。 用于 MAXDOP 限制并行计划执行中使用的处理器数。 最大数量为 64 个处理器。

max_degree_of_parallelism 可以是：

  • 1：取消并行计划生成。
  • >1：将并行索引操作中使用的最大处理器数限制为指定的数字。
  • 0 （默认值）：根据当前系统工作负荷使用实际处理器数或更少。

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

注意

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

DATA_COMPRESSION

适用于：SQL Server 2008 (10.0.x) 及更高版本

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

  • 无

    不压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。

  • ROW

    使用行压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。

  • PAGE

    使用页压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。

  • COLUMNSTORE

    适用于：SQL Server 2014 (12.x) 及更高版本

    仅适用于列存储表。 COLUMNSTORE 指定使用选项解压缩已 COLUMNSTORE_ARCHIVE 压缩的分区。 还原数据后， COLUMNSTORE 索引将继续使用用于所有列存储表的列存储压缩进行压缩。

  • COLUMNSTORE_ARCHIVE

    适用于：SQL Server 2014 (12.x) 及更高版本

    仅适用于列存储表，这是使用聚集列存储索引存储的表。 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 ] )

适用于：SQL Server 2008 (10.0.x) 及更高版本

指定应用 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 选项，例如：

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

single_partition_rebuild__option><

在大多数情况下，重新生成索引也将重新生成已分区索引的所有分区。 下面的选项在应用于单个分区时不会重新生成所有分区。

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

适用于：SQL Server 2014 (12.x) 及更高版本

一旦此表没有阻塞操作，就会 SWITCH 立即完成联机索引重新生成。 WAIT_AT_LOW_PRIORITY指示如果SWITCH无法立即完成或联机索引重新生成操作，它将等待。 该操作持有低优先级锁，允许持有与 DDL 语句冲突的锁的其他操作继续进行。 省略 WAIT AT LOW PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

MAX_DURATION = 时间 [ 分钟 ]

必须获取的或联机索引重新生成锁的等待时间（以分钟为单位指定的整数值）， SWITCH 在执行 DDL 命令时等待。 SWITCH或联机索引重新生成操作将尝试立即完成。 如果操作在一段时间内被 MAX_DURATION 阻止，则会执行其中 ABORT_AFTER_WAIT 一个操作。 MAX_DURATION 时间始终以分钟为单位，可以省略单词 MINUTES 。

ABORT_AFTER_WAIT = { NONE |标准版LF |BLOCKERS }

  • NONE

    SWITCH继续执行或联机索引重新生成操作，而无需更改锁定优先级（使用常规优先级）。

  • SELF

    SWITCH退出当前正在执行的或联机索引重新生成 DDL 操作，而无需执行任何操作。

  • BLOCKERS

    终止阻止当前 SWITCH 或联机索引重新生成 DDL 操作的所有用户事务，以便该操作可以继续。

    BLOCKERS 需要 ALTER ANY CONNECTION 权限。

相关内容