ALTER INDEX (Transact-SQL)

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 数据库

通过禁用、重新生成或重新组织索引,或通过设置索引选项,修改现有的表索引或视图索引(行存储、列存储或 XML)。

Transact-SQL 语法约定

Syntax

SQL Server、Azure SQL 数据库和Azure SQL 托管实例的语法。

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD [
            [ WITH ( <rebuild_index_option> [ , ...n ] ) ]
          | [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      ]
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

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

<rebuild_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 }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

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

Syntax for Azure Synapse Analytics.

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD [
            [ WITH ( <rebuild_index_option> [ , ...n ] ) ]
          | [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      ]
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Arguments

index_name

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

ALL

指定与表或视图相关联的所有索引,而不考虑是什么索引类型。 如果一个或多个索引处于脱机或只读文件组中,或者不允许对一个或多个索引类型执行指定操作,则 ALL 指定会导致语句失败。 下表列出了索引操作和不允许使用的索引类型。

将关键字 ALL 用于此作 如果表有一个或多个,语句会失败
REBUILD WITH ONLINE = ON XML 索引

空间索引

SQL Server 2017(14.x)和较旧版本中的列存储索引。 更高版本支持联机重新生成列存储索引。
REBUILD PARTITION = <partition_number> 未分区的索引、XML 索引、空间索引或已禁用的索引
REORGANIZE 设置为 的 ALLOW_PAGE_LOCKS 索引 OFF
REORGANIZE PARTITION = <partition_number> 未分区的索引、XML 索引、空间索引或已禁用的索引
IGNORE_DUP_KEY = ON XML 索引

空间索引

列存储索引
ONLINE = ON XML 索引

空间索引

列存储索引
RESUMABLE = ON ALL 关键字不支持恢复索引

如果使用 ALL 指定 PARTITION = <partition_number>,则必须对齐所有索引。 这意味着,它们是基于等同的分区函数进行分区的。 使用 ALL with PARTITION 会导致重新生成或重新组织具有相同的所有索引分区 <partition_number> 。 有关已分区索引的详细信息,请参阅 已分区表和索引。

有关联机索引作的详细信息,请参阅 联机索引作指南。

database_name

数据库的名称。

schema_name

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

table_or_view_name

与该索引关联的表或视图的名称。 若要查看表或视图的索引详细信息,请使用 sys.indexes 目录视图。

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

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ] ]

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

指定使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引。 REBUILD 启用已禁用的索引。 除非指定了关键字 ALL ,否则重新生成聚集索引不会重新生成关联的非聚集索引。 如果未指定索引选项,则应用 sys.indexes 中的现有索引选项值。 对于值未出现在 sys.indexes的任何索引选项,选项的参数定义中指示的默认选项适用。

如果指定了 ALL 并且基础表是堆,则重新生成作对堆没有影响。 重新生成与表相关联的所有非聚集索引。

如果数据库恢复模式为大容量日志或简单模式,则可以最小记录 REBUILD作。

重新生成主 XML 索引时,基础用户表在索引操作持续期间不可用。

对于列存储索引,重新生成作:

  • 将所有数据重新压缩到列存储中。 重新生成作正在进行时存在列存储索引的两个副本。 重新生成完成后,数据库引擎将删除原始列存储索引。
  • 对于有序列存储索引,在子句的 ORDER 列上再次排序数据。 最终的订单质量取决于与指数创建时相同的因素。 有关详细信息,请参阅 减少段重叠并提高查询性能。
  • 对于未包含该 ORDER 子句创建的列存储索引,不保留数据的现有顺序,例如从聚类行存储索引创建列存储索引所获得的顺序。 要引入列段顺序,请使用语 CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON) 句。 有关详细信息,请参阅 CREATE COLUMNSTORE INDEX 和 使用有序列存储索引进行性能优化。

有关索引维护的详细信息,请参阅优化索引维护以提高查询性能并减少资源消耗。

PARTITION

指定只重新生成或重新组织索引的一个分区。 PARTITION 如果 index_name 不是分区索引,则无法指定。

PARTITION = ALL 重建所有分区。

Warning

在具有 1,000 多个分区的表上创建和重新生成非对齐索引是可能的,但不受支持。 这样做可能会导致这些操作期间性能下降或内存消耗过多。 Microsoft建议仅在分区数超过 1,000 时使用对齐索引。

  • partition_number

    要重新生成或重新组织已分区索引的分区数。 partition_number 是一个可以引用变量的常量表达式。 其中包括用户定义类型变量或函数以及用户定义函数,但不能引用 Transact-SQL 语句。 partition_number 必须存在,否则语句失败。

  • WITH ( <single_partition_rebuild_index_option> )

    SORT_IN_TEMPDB MAXDOP XML_COMPRESSION DATA_COMPRESSION、和 是使用语(PARTITION = partition_number)法重建单个分区时可以指定的选项。 不能在单个分区重新生成作中指定 XML 索引。

DISABLE

将索引标记为已禁用,从而不能由 数据库引擎使用。 可禁用任何索引。 已禁用的索引的索引定义保留在没有基础索引数据的系统目录中。 禁用聚集索引将阻止用户访问基础表数据。 若要启用索引,请使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。 有关详细信息,请参阅 禁用索引和约束,启用索引和约束。

对行存储索引执行 REORGANIZE

对于行存储索引, REORGANIZE 指定重新组织索引叶级别。 操作 REORGANIZE 为:

  • 始终联机执行。 这意味着长期阻塞表锁不会保留,并且基础表中的数据的查询或更新可以在 ALTER INDEX REORGANIZE 事务期间继续。
  • 不允许用于禁用的索引。
  • 设置为 ALLOW_PAGE_LOCKS..OFF
  • 在事务中执行且事务回滚时,不会回滚。

Note

ALTER INDEX REORGANIZE 使用显式事务(例如 ALTER INDEX 中的 BEGIN TRAN ... COMMIT/ROLLBACK)而不是默认隐式事务模式时,REORGANIZE 的锁定行为将更加严格,这可能会导致阻塞。 关于隐式交易的更多信息,请参见 SET IMPLICIT_TRANSACTIONS。

有关详细信息,请参阅 优化索引维护以提高查询性能并减少资源消耗。

REORGANIZE WITH ( LOB_COMPACTION = { ON |OFF }

适用于行存储索引。

  • ON

    • 规定要压缩所有包含这些大型对象(LOB)数据类型的页面:image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、json 和 xml。 压缩这些数据可以减少磁盘上的数据大小。
    • 对于聚集索引,这会压缩表中包含的所有 LOB 列。
    • 对于非聚集索引,这会压缩作为索引中非键(已包括)列的所有 LOB 列。
    • REORGANIZE ALL 对所有索引执行 LOB 压缩。 对于每个索引,这会压缩聚集索引、基础表中的所有 LOB 列 或是非聚集索引中的包含列。
  • OFF

    • 不压缩包含大型对象数据的页。
    • OFF 对堆没有影响。

对列存储索引执行 REORGANIZE

对于列存储索引,REORGANIZE 将每个关闭的增量行组压缩到列存储中作为压缩行组。 REORGANIZE 操作始终联机执行。 也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。

有关详细信息,请参阅 优化索引维护以提高查询性能并减少资源消耗。

  • REORGANIZE 移动封闭的 delta 行组到压缩行组时并非必需。 后台元组移动器 (TM) 进程会定期唤醒以压缩关闭的增量行组。 建议在元组移动器落后时使用 REORGANIZE 。 REORGANIZE 可以更激进地压缩行组。
  • 若要压缩所有打开和关闭的行组,请参阅 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS)。

对于 SQL Server 2016(13.x)及更高版本中的列存储索引,Azure SQL 数据库和 Azure SQL 托管实例 REORGANIZE 联机执行以下额外的碎片整理优化:

  • 在逻辑上删除了 10 个% 行或更多行时,从行组中物理删除已删除的行。 删除的字节会在物理媒体上进行回收。 例如,如果压缩行组 100 万行删除了 100,000 行,则数据库引擎将删除已删除的行,并使用 900,000 行重新压缩行组。

  • 合并一个或多个压缩行组,以将每个行组的行增加到最多 1,048,576 行。 例如,如果批量导入 5 批 102,400 行,则会收到 5 个压缩行组。 如果运行 REORGANIZE,这些行组将合并到包含 512,000 行的 1 个压缩行组中。 这假定没有字典大小或内存限制。

  • 对于逻辑删除了 10 个% 行或更多行的行组,数据库引擎会尝试将此行组与一个或多个行组合并。 例如,行组 1 使用 500,000 行进行压缩,行组 21 使用最大值 1,048,576 行进行压缩。 行组 21 删除了 60% 的行,剩下 409,830 行。 数据库引擎倾向于组合这两个行组,以压缩包含 909,830 行的新行组。

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON |OFF }

适用于列存储索引。

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

COMPRESS_ALL_ROW_GROUPS 提供一种强制打开或关闭 delta 行组到列存储的方法。 使用此选项时,无需重新生成列存储索引即可清空增量行组。 结合其他删除和合并碎片整理功能,这使得在大多数情况下不再需要重新生成列存储索引。

  • ON

    强制所有行组进入列存储,而不考虑大小和状态(已关闭或打开)。

  • OFF

    强制所有已关闭的行组进入列存储。

有关详细信息,请参阅 优化索引维护以提高查询性能并减少资源消耗。

SET ( <set_index选项> [ ,... n ] )

在不重新生成或重新组织索引的情况下修改索引选项。 SET 无法为已禁用的索引指定。

PAD_INDEX = { ON |OFF }

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

  • ON

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

  • OFF

    考虑到中间页上的键集,中间级页面填充到接近容量,为索引可以具有的最大大小的至少一行留出足够的空间。 如果 PAD_INDEX 设置为 ON 但未指定填充因子,则也会发生这种情况。

有关详细信息,请参阅 CREATE INDEX。

FILLFACTOR = fillfactor

指定一个百分比,指示在数据库引擎创建或修改索引的过程中,应将每个索引页面的叶级填充到什么程度。 填充因子值必须是一个介于 1 至 100 之间的整数值。 默认值为 0。 填充因子的值 0 和 100 在所有方面都是相同的。

显式的 FILLFACTOR 设置仅在首次创建或重新生成索引时应用。 数据库引擎并不会在页中动态保持指定的可用空间百分比。 有关详细信息,请参阅 CREATE INDEX。

若要查看填充因子设置,请使用 fill_factor 中的 sys.indexes。

Important

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

SORT_IN_TEMPDB = { ON |OFF }

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

  • ON

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

  • OFF

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

如果不需要执行排序操作,或者可以在内存中执行排序,则忽略 SORT_IN_TEMPDB 选项。

有关详细信息,请参阅索引 SORT_IN_TEMPDB 选项。

IGNORE_DUP_KEY = { ON |OFF }

指定在插入操作尝试向唯一索引插入重复键值时的错误响应。 IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。 默认为 OFF。

  • ON

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

  • OFF

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

IGNORE_DUP_KEY对于视图上创建的索引、非唯一索引、XML 索引、空间索引和筛选索引,无法设置为。ON

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

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

STATISTICS_NORECOMPUTE = { ON |OFF }

为索引上的统计信息禁用或启用自动统计信息更新选项 AUTO_STATISTICS_UPDATE。 默认为 OFF。

  • ON

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

  • OFF

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

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

Warning

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

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

STATISTICS_INCREMENTAL = { ON |OFF }

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

ON时,在索引上创建的统计信息是按分区统计信息创建的。 OFF时,将删除现有统计信息,数据库引擎重新计算统计信息。 默认为 OFF。

如果每个分区统计信息不受支持,则会忽略该选项并生成警告。 在以下情况下不支持增量统计信息:

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

ONLINE = { ON |OFF }

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

对于 XML 索引或空间索引,仅 ONLINE = OFF 受支持,如果 ONLINE 设置为 ON 错误,则引发错误。

Important

在 Microsoft SQL Server 的各版本中均不提供联机索引操作。 有关SQL Server各版本支持的功能列表,请参见SQL Server 2025的版本及支持功能。

  • ON

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

    Note

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

  • OFF

    表锁在索引作的持续时间内应用。 离线行商店索引重建在操作期间会在表上获得模式修改(Schema Modification (Sch-M)锁。 这防止其他会话读取或修改表中的数据,或更改其定义,直到重建完成。

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

可以联机重新生成索引(包括全局临时表中的索引),但以下情况除外:

  • 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 选项生成或重新生成包含其他列的聚集索引。

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

可恢复 = { ON |OFF}

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

指定联机索引操作是否可恢复。

  • ON

    索引操作可恢复。

  • OFF

    索引操作不可恢复。

MAX_DURATION = 时间 [ 分钟] 用于 RESUMABLE = ON (需要 ONLINE = ON)

适用于:SQL Server 2017(14.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

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

尽管所有 XML 索引和空间索引都以语法方式支持 MAXDOP 选项,但 ALTER INDEX 目前只使用单个处理器。

max_degree_of_parallelism 可以是:

  • 1

    取消并行计划生成。

  • >1

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

  • 0(默认值)

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

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

Note

并行索引操作并不适用于 SQL Server 的所有版本。 有关SQL Server各版本支持的功能列表,请参见SQL Server 2025的版本及支持功能。

COMPRESSION_DELAY = { 0 | 持续时间 [ 分钟 ] }

适用于:SQL Server(从 SQL Server 2016 (13.x)起)、Azure SQL 数据库 和 Azure SQL 托管实例

对于具有列存储索引的基于磁盘的表,指定处于关闭状态的增量行组必须保留在增量存储中的最小分钟数,然后数据库引擎才能将其压缩为压缩行组。 由于基于磁盘的表不会跟踪各个行的插入和更新时间,因此数据库引擎将此延迟仅应用于处于关闭状态的增量存储行组。

默认为 0 分钟。

有关何时使用 COMPRESSION_DELAY的建议,请参阅 列存储索引入门,了解实时作分析。

DATA_COMPRESSION

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

  • NONE

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

  • ROW

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

  • PAGE

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

  • COLUMNSTORE

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

    仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。 指定 COLUMNSTORE 会删除所有其他数据压缩,包括 COLUMNSTORE_ARCHIVE。

  • 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

    不压缩索引或指定的分区。

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)
);

RESUME

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

恢复手动暂停的索引作,因为达到最大持续时间或失败。

  • MAX_DURATION

    指定在恢复后恢复索引作的时长(以整数分钟为单位)。

  • WAIT_AT_LOW_PRIORITY

    暂停后恢复索引生成作必须获取必要的锁。 WAIT_AT_LOW_PRIORITY 表示索引构建操作获得低优先级锁,允许在索引构建操作等待时继续其他操作。 省略 WAIT_AT_LOW_PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。 有关详细信息,请参阅 WAIT_AT_LOW_PRIORITY。

PAUSE

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

暂停可恢复索引生成作。

ABORT

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

中止已启动为可恢复的正在运行或暂停的索引生成作。 必须显式执行 ABORT 命令才能终止可恢复索引生成作。 可恢复索引作中的失败或暂停不会终止其执行;相反,它会使作处于无限期的暂停状态。

Remarks

ALTER INDEX 不能用于重新分区索引或将其移动到不同的文件组。 此语句不能用于修改索引定义,如添加或删除列,或更改列的顺序。 将 CREATE INDEX 与 DROP_EXISTING 子句配合使用,执行下列操作。

未显式指定选项时,则应用当前设置。 例如,如果未 FILLFACTOR 在 REBUILD 子句中指定设置,则会在重新生成过程中使用系统目录中存储的填充因子值。 若要查看当前索引选项设置,请使用 sys.indexes。

ONLINE、MAXDOP 和 SORT_IN_TEMPDB 的值不存储在系统目录中。 除非在索引语句中指定,否则,将使用选项的默认值。

在多处理器计算机中,就像其他查询那样,ALTER INDEX REBUILD 自动使用更多处理器来执行与修改索引相关联的扫描和排序操作。 相反,ALTER INDEX REORGANIZE 是单线程作。 有关详细信息,请参阅 配置并行索引作。

在Microsoft Fabric的SQL数据库中, ALTER INDEX ALL 虽然不支持,但 ALTER INDEX <index name> 支持。

重新生成索引

重新生成索引将会删除并重新创建索引。 这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间,然后对连续页中的索引行重新排序。 如果指定 ALL,将删除表中的所有索引,然后在一个事务中重新生成。 不必预先删除外键约束。 重新生成具有 128 个区或更多区的索引时,数据库引擎延迟实际的页释放及其关联的锁,直到事务提交。 有关详细信息,请参阅 延迟解除分配。

有关详细信息,请参阅 优化索引维护以提高查询性能并减少资源消耗。

重组索引

使用最少系统资源重新组织索引。 通过对叶级页以物理方式重新排序,使之与叶节点的从左到右的逻辑顺序相匹配,进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。

如果指定了 ALL,将重新组织表中的关系索引(包括聚集索引和非聚集索引)和 XML 索引。 指定 时,某些 ALL 适用。

有关详细信息,请参阅 优化索引维护以提高查询性能并减少资源消耗。

Note

对于具有有序列存储索引的表,ALTER INDEX REORGANIZE 不会对数据重新排序。 为了分析数据,使用 ALTER INDEX ... REBUILD 或 CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON)。

禁用索引

禁用索引可防止用户访问该索引,对于聚集索引,还可防止用户访问基础表数据。 索引定义保留在系统目录中。 对视图禁用非聚集索引或聚集索引会以物理方式删除索引数据。 禁用聚集索引将阻止对数据的访问,但在删除或重新生成索引之前,数据在 B 树中一直保持未维护的状态。 若要查看索引是否已禁用,请使用 is_disabled 目录视图中的 sys.indexes 列。

Note

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

如果表位于事务复制发布中,则不能禁用与主键约束关联的索引。 复制需要使用这些索引。 若要禁用此类索引,必须先从发布中删除表。 有关详细信息,请参阅 发布数据和数据库对象。

使用 ALTER INDEX REBUILD 语句或 CREATE INDEX WITH DROP_EXISTING 语句来启用索引。 不能使用选项设置为 ONLINE 重新生成ON禁用的聚集索引。 有关详细信息,请参阅 “禁用索引和约束”。

SET 选项

可以在不重新生成或重新组织该索引的情况下设置指定索引的选项ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEYIGNORE_DUP_KEYSTATISTICS_NORECOMPUTE选项和索引。 修改的值立即应用于索引。 若要查看这些设置,请使用 sys.indexes。 有关详细信息,请参阅 设置索引选项。

行锁和页锁选项

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

如果 ALLOW_ROW_LOCKS = OFF 并且 ALLOW_PAGE_LOCK = OFF,则当访问索引时将仅允许表级别的锁。

设置行锁或页锁选项时,如果指定了 ALL,这些设置将应用于所有索引。 基础表为堆时,通过以下方式应用这些设置:

Option 适用于
ALLOW_ROW_LOCKS = ON 或 OFF 堆和所有关联的非聚集索引。
ALLOW_PAGE_LOCKS = ON 堆和所有关联的非聚集索引。
ALLOW_PAGE_LOCKS = OFF 不允许所有页锁的非聚集索引。 对于堆,不允许仅共享(S)、更新(U)和独占(X)页锁。 出于内部目的,数据库引擎仍可获取意向页锁(IS、IU或 IX)。

Warning

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

联机索引操作

重新生成索引并将 ONLINE 选项设置为 ON时,索引中的数据、其关联的表和其他索引可用于查询和修改。 您也可以联机重新生成单个分区上某索引的一部分。 独占表锁仅在索引重新生成结束时保留短时间。

重新组织索引始终联机执行。 此过程仅保留短时间内的锁,并且不太可能阻止查询或更新。

只有在执行以下操作时,才能对同一表或表分区执行并发联机索引操作:

  • 创建多个非聚集索引。
  • 在同一个表中重新组织不同索引。
  • 在同一个表中重新生成不重叠的索引时,重新组织不同的索引。

同一时间执行的所有其他联机索引操作都将失败。 例如,您不能在同一个表中同时重新生成两个索引或更多索引,也不能在同一个表中重新生成现有索引时创建新的索引。

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

可恢复索引操作

适用于:SQL Server 2017(14.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> 命令。
  • 如果使用相同的参数重新执行原始 ALTER INDEX REBUILD 语句,会恢复暂停的索引重新生成操作。 还可通过执行 ALTER INDEX RESUME 语句来恢复暂停的索引重新生成操作。
  • ABORT 命令会终止运行索引生成的会话,并取消索引作。 无法恢复已中止的索引作。
  • 恢复暂停的索引重新生成作时,可以将 MAXDOP 值更改为新值。 如果在恢复暂停的索引作时未指定 MAXDOP,则使用用于上次恢复的 MAXDOP 值。 如果未为索引重新生成作指定 MAXDOP 选项,则使用默认值。

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

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

Resources

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

  • 保留要生成的索引所需的额外空间,包括暂停生成的时间。
  • 排序阶段的其他日志吞吐量。 与常规联机索引重新生成相比,可恢复索引的总体日志空间使用率较低,并允许在此作期间截断日志。
  • 不允许在暂停索引作时修改正在重新生成的索引或其关联表的 DDL 语句。
  • 在暂停作和作运行时,内部生成索引上会阻止虚影清理。
  • 如果表包含 LOB 列,则可恢复的聚集索引生成需要在作开始时锁定架构修改(Sch-M)。

当前功能限制

可恢复索引重新生成作具有以下限制:

  • 可恢复索引作不支持 SORT_IN_TEMPDB = ON 选项。
  • 不能在显式事务内执行具有 RESUMABLE = ON 的 DDL 命令。
  • 如果索引包含以下项,则无法使用可恢复索引作重新生成索引:
    • 作为键列的计算列或 timestamp (rowversion) 列。
    • 作为包含列的计算列或 LOB 列。
  • 不支持恢复索引作:
    • ALTER INDEX REBUILD ALL 命令
    • ALTER TABLE REBUILD 命令
    • 列存储索引
    • 筛选索引
    • 禁用的索引

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

适用于:SQL Server 2014(12.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= [NONEBLOCKERS | SELF | ]

  • 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

空间索引限制

重新生成空间索引时,基础用户表在索引作期间不可用。

PRIMARY KEY在对该表的列定义空间索引时,无法修改用户表中的约束。 若要更改 PRIMARY KEY 约束,请先删除表的每个空间索引。 修改 PRIMARY KEY 约束后,可以重新创建每个空间索引。

在单个分区重新生成操作中,无法指定任何空间索引。 但是,可以在表重新生成中指定空间索引。

若要更改特定于某个空间索引的选项(例如 BOUNDING_BOX 或 GRID),可使用 CREATE SPATIAL INDEX 语句指定 DROP_EXISTING = ON,或者删除该空间索引,再新建一个。 有关示例,请参阅 CREATE SPATIAL INDEX。

数据压缩

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

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

  • 压缩可以允许将更多行存储在页面上,但不会更改最大行大小。
  • 索引的非叶页不是页面压缩的,但可以行压缩。
  • 每个非聚集索引都有单个压缩设置,并且不继承基础表的压缩设置。
  • 在堆上创建聚集索引时,除非指定了替代压缩状态,否则聚集索引将继承堆的压缩状态。

以下注意事项适用于重新生成已分区索引:

  • 如果表具有非对齐索引,则无法更改单个分区的压缩设置。
  • ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... 语法使用指定的压缩选项重新生成索引的指定分区。 如果省略 WITH DATA_COMPRESSION 子句,则使用现有的压缩选项。
  • ALTER INDEX <index> ... REBUILD PARTITION = ALL 语法使用现有压缩选项重新生成索引的所有分区。
  • ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) 语法可重新生成索引的所有分区。 可以使用 DATA_COMPRESSION = ... ON PARTITIONS ( ...) 子句为不同的分区选择不同的压缩。

若要评估更改 PAGE 和 ROW 压缩如何影响表、索引或分区,请使用 sp_estimate_data_compression_savings 存储过程。

统计信息

重新生成索引时,索引上的统计信息会通过对非分区索引进行完全扫描和已分区索引的默认采样比率进行更新。 在索引重新生成过程中,不会更新表的其他统计信息。

Permissions

需要对表或视图具有 ALTER 权限。

版本说明

  • Azure SQL 数据库不支持除 PRIMARY以外的文件组。
  • Azure SQL 数据库和 Azure SQL 托管实例不支持 FILESTREAM 选项。
  • 列存储索引在 SQL Server 2012(11.x)之前不可用。
  • SQL Server 2017(14.x)及更高版本、Azure SQL 数据库和 Azure SQL 托管实例中提供了可恢复索引作。

基本语法示例

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

示例:列存储索引

这些示例适用于列存储索引。

A. 重新组织演示

此示例演示 ALTER INDEX REORGANIZE 命令的工作原理。 它创建一个具有多个行组的表,然后演示 REORGANIZE 如何合并行组。

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

使用 TABLOCK 选项并行插入行。 从 SQL Server 2016 (13.x)开始, INSERT INTO 该操作可以在使用时 TABLOCK 并行运行。

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

运行以下命令以查看 OPEN 增量行组。 行组数取决于并行度。

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

运行以下命令以强制所有 CLOSED 行 OPEN 组进入列存储。

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

再次运行此命令,可以看到较小的行组已合并到一个压缩行组中。

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. 将关闭的增量行组压缩到列存储中

此示例使用 REORGANIZE 选项将每个 CLOSED 增量行组压缩到列存储中作为压缩行组。 这不是必要的,但在元组移动器不够快地压缩 CLOSED 行组时非常有用。

可在 AdventureWorksDW2025 示例数据库中运行这两个示例。

此示例 REORGANIZE 在所有分区上运行。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

此示例 REORGANIZE 在特定分区上运行。

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. 将所有打开和关闭的增量行组压缩到列存储中

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

该命令 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) 将每个 OPEN 行组和 CLOSED 增量行组作为压缩行组压缩到列存储中。 这会清空增量存储,并强制所有行压缩到列存储中。 这在执行许多插入操作之后特别有用,因为这些操作将行存储在一个或多个增量行组中。

REORGANIZE 将 <行组合并以填充行组,最大行数 = 1,048,576。 因此,当你压缩所有 OPEN 行组和 CLOSED 行组时,最终不会包含大量只包含几个行的压缩行组。 需要行组尽可能满,以减少压缩大小并提高查询性能。

下面的示例使用 AdventureWorksDW2025 数据库。

此示例将所有 OPEN 行组和 CLOSED 增量行组移到列存储索引中。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

此示例将所有 OPEN 行组和 CLOSED 增量行组移到特定分区的列存储索引中。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. 对列存储索引进行联机碎片整理

不适用于:SQL Server 2012(11.x)和 SQL Server 2014(12.x)。

从 SQL Server 2016 (13.x)开始,除了将增量行组压缩到列存储之外, REORGANIZE 它还执行联机碎片整理。 首先,它会在删除了行组中 10% 或更多行时物理移除已删除的行,从而减少列存储的大小。 然后,它将行组合并在一起以形成更大行组,每个行组最多包含 1,024,576 行。 更改的所有行组都会重新压缩。

Note

从 SQL Server 2016(13.x 开始),在大多数情况下,重新生成列存储索引不再需要,因为 REORGANIZE 从物理上删除已删除的行和合并行组。 该 COMPRESS_ALL_ROW_GROUPS 选项强制所有 OPEN 行组或 CLOSED 增量行组进入列存储中,这些列存储以前只能通过重新生成来完成。 REORGANIZE 处于在线状态,并在后台执行,以便查询在操作进行时能够继续。

以下示例通过物理删除已从表中删除的行并合并行组,对索引进行 REORGANIZE 碎片整理。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. 脱机重新生成聚集列存储索引

适用于:SQL Server、Azure SQL 数据库和 Azure SQL 托管实例

Tip

自 SQL Server 2016 (13.x) 起且在 Azure SQL 数据库 中,建议对列存储索引使用 ALTER INDEX REORGANIZE 而不是 ALTER INDEX REBUILD。

Note

在 SQL Server 2012(11.x)和 SQL Server 2014(12.x)中, REORGANIZE 仅用于将行组压缩 CLOSED 到列存储中。 执行碎片整理操作以及将所有增量行组都强制到列存储中的唯一方法是重新生成索引。

此示例演示如何重新生成聚集列存储索引以及将所有增量行组都强制到列存储中。 这是第一步,将准备具有一个聚集列存储索引的 FactInternetSales2 数据库中的表 AdventureWorksDW2025并插入来自前 4 列的数据。

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

结果显示一个 OPEN 行组,这意味着 SQL Server 在关闭行组并将数据移动到列存储之前等待添加更多行。 下一个语句重新生成聚集列存储索引,这会将所有行强制到列存储中。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

该语句的结果 SELECT 显示行组, COMPRESSED这意味着行组的列段现在已压缩并存储在列存储中。

F. 脱机重新生成聚集列存储索引的分区

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

若要重新生成大型聚集列存储索引的分区,请使用带有分区选项的 ALTER INDEX REBUILD。 此示例重新生成分区 12。 从 SQL Server 2016 (13.x) 开始,建议将 REBUILD 替换为 REORGANIZE。

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. 更改聚集列存储索引以使用存档压缩

不适用于:SQL Server 2012(11.x)

可以选择使用 COLUMNSTORE_ARCHIVE 数据压缩选项进一步减小聚集列存储索引的大小。 这对于要保留在较廉价存储上的较旧数据十分实用。 建议仅对不经常访问的数据使用此功能,因为解压缩速度比正常 COLUMNSTORE 压缩速度慢。

下面的示例重新生成一个聚集列存储索引以便使用存档压缩,然后显示如何删除该存档压缩。 最终结果仅使用列存储压缩。

首先,通过创建具有聚集列存储索引的表来准备示例。 然后,使用存档压缩进一步压缩表。

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

此示例会删除存档压缩,并且仅使用列存储压缩。

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

示例:行存储索引

A. 重新生成索引

下面的示例在 Employee 数据库的 AdventureWorks2025 表中重新生成单个索引。

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. 重新生成表的所有索引并指定选项

下面的示例指定关键字 ALL。 这将重新生成与 Production.Product 数据库中的表 AdventureWorks2025 相关联的所有索引。 其中指定了三个选项。

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

下面的示例添加包含低优先级锁选项的 ONLINE 选项,并添加行压缩选项。

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

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. 通过 LOB 压缩重新组织索引

下面的示例重新整理 AdventureWorks2025 数据库中的单个聚集索引。 因为该索引在叶级别包含 LOB 数据类型,所以该语句还会压缩所有包含该大型对象数据的页。 不需要指定 WITH (LOB_COMPACTION = ON) 选项,因为默认值为 ON。

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. 设置索引上的选项

下面的示例为 AK_SalesOrderHeader_SalesOrderNumber 数据库中的索引 AdventureWorks2025 设置了几个选项。

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. 禁用索引

下面的示例禁用了对 Employee 数据库中的 AdventureWorks2025 表的非聚集索引。

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 禁用约束

以下示例通过禁用数据库中的PRIMARY KEY索引PRIMARY KEY来禁用AdventureWorks2025约束。 基础 FOREIGN KEY 表的约束会自动禁用,并显示警告消息。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

结果集返回此警告消息。

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. 启用约束

以下示例启用在示例 F 中禁用的 PRIMARY KEY 和 FOREIGN KEY 约束。

通过 PRIMARY KEY 重新生成 PRIMARY KEY 索引来启用约束。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

FOREIGN KEY然后启用约束。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. 重新生成已分区索引

下面的示例在 5 数据库中重新生成一个分区索引为 IX_TransactionHistory_TransactionDate 的分区,分区号为 AdventureWorks2025。 分区 5 是使用 ONLINE=ON 重新生成的,并且对索引重新生成操作获取的每个锁分别应用低优先级锁的 10 分钟等待时间。 如果在此时间无法获取锁来完成索引重新生成,重新生成操作语句本身就会由于 ABORT_AFTER_WAIT = SELF 而中止。

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

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. 更改索引的压缩设置

下面的示例重新生成未分区行存储表的索引。

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. 使用 XML 压缩更改索引的设置

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

下面的示例重新生成未分区行存储表的索引。

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

有关更多数据压缩示例,请参阅 数据压缩。

K. 联机可恢复索引重新生成

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

下面的示例演示如何使用联机可恢复索引重新生成。

使用 MAXDOP = 1.. 执行联机索引重新生成作为可恢复操作。 暂停索引操作后再次执行相同的命令,自动恢复索引重新生成操作。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

将联机索引重新生成为可恢复操作,设置为 MAX_DURATION 240 分钟。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

暂停正在运行的可恢复联机索引重新生成。

ALTER INDEX test_idx on test_table PAUSE;

对于作为可恢复操作执行的索引重新生成,恢复联机索引重新生成,指定设置为 MAXDOP 4 的新值。

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

对作为可恢复操作执行的索引联机重新生成恢复联机索引重新生成操作。 设置为 MAXDOP 2,将索引的执行时间设置为可恢复 240 分钟,如果锁定上阻止了索引,请等待 10 分钟,然后终止所有阻止程序。

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

中止正在运行或暂停的可恢复索引重新生成操作。

ALTER INDEX test_idx on test_table ABORT;