ALTER INDEX (Transact-SQL)

项目
05/23/2024

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

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

语法

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

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ 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 } )
}

Azure Synapse Analytics 和分析平台系统的语法（PDW）。

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ 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 }
}

注意

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

参数

index_name

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

ALL

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

将关键字 ALL 与此操作一起使用	如果表有一个或多个，语句会失败
`REBUILD WITH ONLINE = ON`	XML 索引空间索引列存储索引 ¹
`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`²	关键字 (keyword)不支持`ALL`恢复索引

¹ 适用于 SQL Server 2012（11.x）及更高版本，以及Azure SQL 数据库。

² 适用于 SQL Server 2017（14.x）及更高版本，以及Azure SQL 数据库

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

有关可联机执行的索引操作的更多详细信息，请参阅联机索引操作指南。

database_name

数据库的名称。

schema_name

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

table_or_view_name

与该索引关联的表或视图的名称。若要显示对象的索引报表，请使用 sys.indexes 目录视图。

当database_name是当前数据库或database_name并且table_or_view_nametempdb以#开头时，SQL 数据库支持由三部分构成的名称格式<database_name>.[schema_name].<table_or_view_name>。

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

适用于：SQL Server 2012 (11.x) 及更高版本和 Azure SQL 数据库

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

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

如果数据库恢复模式设置为大容量日志记录或简单日志记录，则可对 REBUILD 操作进行最小日志记录。

注意

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

对于列存储索引，REBUILD 操作：

不使用排序顺序。
在执行 REBUILD 时获取表或分区上的排他锁。数据处于“脱机”状态，即使在使用NOLOCK读取提交快照隔离（富通信I）或快照隔离（SI）期间也不可用REBUILD。
将所有数据重新压缩到列存储中。在进行 REBUILD 时存在列存储索引的两个副本。 REBUILD 完成后，SQL Server 将删除原始列存储索引。

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

PARTITION

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

PARTITION = ALL 重新生成所有分区。

警告

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

partition_number

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

SORT_IN_TEMPDB、MAXDOPDATA_COMPRESSION 和 XML_COMPRESSION 是在 REBUILD 单个分区 (PARTITION = partition_number) 时可指定的选项。不能在单分区 REBUILD 操作中指定 XML 索引。

DISABLE

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

对行存储索引执行 REORGANIZE

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

始终联机执行。也就是说，不保留长期阻塞性表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。
不允许用于禁用的索引。
设置为 OFF..ALLOW_PAGE_LOCKS
在事务中执行且事务回滚时，不会回滚。

注意

ALTER INDEX REORGANIZE 使用显式事务（例如 BEGIN TRAN ... COMMIT/ROLLBACK 中的 ALTER INDEX）而不是默认隐式事务模式时，REORGANIZE 的锁定行为将更加严格，这可能会导致阻塞。有关隐式事务的详细信息，请参阅标准版T IMPLICIT_TRANSACTIONS。

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

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

适用于行存储索引。

LOB_COMPACTION = ON

指定要压缩包含以下这些大型对象 (LOB) 数据类型的数据的所有页面：图像、文本、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。压缩这些数据可以减少磁盘上的数据大小。
对于聚集索引，这会压缩表中包含的所有 LOB 列。
对于非聚集索引，这会压缩作为索引中非键（已包括）列的所有 LOB 列。
REORGANIZE ALL 对所有索引执行 LOB_COMPACTION。对于每个索引，这会压缩聚集索引、基础表中的所有 LOB 列或是非聚集索引中的包含列。

LOB_COMPACTION = OFF

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

对列存储索引执行 REORGANIZE

对于列存储索引， REORGANIZE 将每个 CLOSED 增量行组压缩到列存储中作为压缩行组。 REORGANIZE 操作始终联机执行。也就是说，不保留长期阻塞性表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。有关详细信息，请参阅优化索引维护以提高查询性能并减少资源消耗。

REORGANIZE 不需要将增量行组移动到 CLOSED 压缩行组中。后台元组移动器（TM）进程会定期唤醒以压缩 CLOSED 增量行组。建议在元组移动器落后时使用 REORGANIZE 。 REORGANIZE 可以更积极地压缩行组。
若要压缩所有 OPEN 组和 CLOSED 行组，请参阅 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 本节中的选项。

对于 SQL Server 中的列存储索引（从 SQL Server 2016 （13.x）开始）和Azure SQL 数据库，REORGANIZE联机执行以下额外的碎片整理优化：

在逻辑删除了 10% 或更多行时从行组中物理移除行。删除的字节会在物理媒体上进行回收。例如，如果压缩行组 100 万行删除了 100,000 行，SQL Server 将删除已删除的行，并使用 90 万行重新压缩行组。它通过移除已删除的行来节省存储。
合并一个或多个压缩行组，以将每个行组的行增加到最多 1,048,576 行。例如，如果批量导入 5 批 102,400 行，则会收到 5 个压缩行组。如果运行 REORGANIZE，这些行组将合并到大小为 512,000 行的 1 个压缩行组中。这假定不存在任何字典大小或内存限制。
对于逻辑删除了 10% 或更多行的行组，SQL Server 会尝试将此行组与一个或多个行组合并。例如，行组 1 使用 500,000 行进行压缩，行组 21 使用最大值 1,048,576 行进行压缩。行组 21 删除了 60% 的行，剩下 409,830 行。 SQL Server 倾向于合并这两个行组来压缩一个新行组，这个行组有 909,830 行。

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

适用于列存储索引。

适用于： SQL Server（从 SQL Server 2016 (13.x) 开始）和 Azure SQL 数据库

COMPRESS_ALL_ROW_GROUPS 提供了一种强制 OPEN 或 CLOSED 增量行组进入列存储的方法。使用此选项时，无需重新生成列存储索引即可清空增量行组。此操作与其他移除和合并碎片整理功能相结合，使得在大多数情况下不再需要重新生成索引。

ON 强制所有行组进入列存储，而不考虑大小和状态（CLOSED 或 OPEN）。
OFF 强制所有 CLOSED 行组进入列存储。

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

标准版T （<set_index 选项> [ ,... n ]

指定不重新生成或重新组织索引的索引选项。 SET 不能为禁用的索引指定。

PAD_INDEX = { ON | OFF }

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

ON

FILLFACTOR 指定的可用空间百分比应用于索引的中间级别页面。如果未FILLFACTOR同时PAD_INDEXON指定，则使用存储在 sys.indexes 中的填充因子值。
OFF 或未指定 fillfactor

中间级页已填充到接近容量限制。这样将至少为索引可以基于中间页中的键集拥有的最大大小的一行留出足够的空间。

有关详细信息，请参阅创建索引。

FILLFACTOR = fillfactor

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

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

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

重要

使用 FILLFACTOR 值创建或更改聚集索引会影响数据占用的存储空间量，因为数据库引擎在创建聚集索引时会再分发数据。

SORT_IN_TEMPDB = { ON | OFF }

指定是否将排序结果 tempdb存储在 . 默认值为OFF“超大规模”Azure SQL 数据库除外。对于“超大规模”中的所有索引重新生成操作，无论指定什么选项，SORT_IN_TEMPDB 始终为 ON，除非使用可恢复索引重新生成。

ON

用于生成索引的中间排序结果存储在其中 tempdb。如果 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 < 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。

警告

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

适用于：SQL Server 2014（12.x）及更高版本，以及Azure SQL 数据库

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

如果每个分区统计信息不受支持，则会忽略该选项并生成警告。对于以下统计信息类型，不支持增量统计信息：

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

ONLINE = { ON | OFF } <同样适用于 rebuild_index_option>

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

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

重要

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

ON

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

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

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

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

XML 索引
对本地临时表的索引
视图的初始唯一聚集索引
列存储索引
聚集索引，前提是基础表包含 LOB 数据类型（image、ntext、text）和空间数据类型
varchar(max) 和 varbinary(max) 列不能是索引的一部分。在 SQL Server（自 SQL Server 2012 (11.x) 起）和 Azure SQL 数据库中，当表包含 varchar(max) 或 varbinary(max) 列时，可以使用 ONLINE 选项生成或重新生成包含其他列的聚集索引。当基表包含 varchar(max) 或 varbinary(max) 列时，Azure SQL 数据库不允许使用 ONLINE 选项

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

以下 XEvent 与 ALTER TABLE ... SWITCH PARTITION 和联机索引重新生成相关。

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

联机索引操作的现有 XEvent progress_report_online_index_operation 包括 partition_number 和 partition_id。

RESUMABLE = { ON | OFF}

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库

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

ON

索引操作可恢复。
OFF

索引操作不可恢复。

MAX_DURATION = 时间 [ 分钟] 用于 `RESUMABLE = ON` （需要 `ONLINE = ON`）

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库

指示可恢复联机索引操作在暂停之前执行的时间（以分钟为单位指定的整数值）。

重要

有关可联机执行的索引操作的更多详细信息，请参阅联机索引操作指南。

注意

列存储索引不支持可恢复联机索引重新生成。

ALLOW_ROW_LOCKS = { ON | OFF }

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

ON

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

不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

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

ON

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

不使用页锁。

注意

当设置为 OFF 时ALLOW_PAGE_LOCKS，无法重新组织索引。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

适用于：SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库

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

MAXDOP = max_degree_of_parallelism

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

重要

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

max_degree_of_parallelism 可以是：

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

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

备注

并行索引操作并不适用于 SQL Server 的所有版本。有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

适用于： SQL Server（从 SQL Server 2016 (13.x) 开始）

对于基于磁盘的表，延迟指定状态中的 CLOSED 增量行组必须保留在增量行组中的最小分钟数，然后 SQL Server 才能将其压缩为压缩行组。由于基于磁盘的表不会跟踪各个行上的插入和更新时间，因此 SQL Server 会将延迟应用于处于状态的 CLOSED 增量行组。

默认为 0 分钟。

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

DATA_COMPRESSION

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

无

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

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

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

适用于：SQL Server 2014（12.x）及更高版本，以及Azure SQL 数据库

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

适用于：SQL Server 2014（12.x）及更高版本，以及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)
);

ONLINE = { ON | OFF } <同样适用于 single_partition_rebuild_index_option>

指定索引或基础表的索引分区是否可以联机或脱机重新生成。如果执行 REBUILD ... ONLINE = ON，则在索引操作期间，可使用此表中的数据进行查询和修改数据。默认为 OFF。

ON

长期表锁在索引操作期间不保留。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。开始索引重新生成时，表上需要一个架构稳定性 (Sch-S) 锁；联机索引重新生成结束。表上需要一个架构修改 (Sch-M) 锁。不过这两个元数据锁持续时间都很短，特别是 Sch-M 锁必须等待所有阻塞事务完成。在等待期间，Sch-M 锁在访问同一表时阻止在此锁后等待的所有其他事务。

注意

联机索引重新生成操作可设置 low_priority_lock_wait 选项，请参阅具有联机索引操作的 WAIT_AT_LOW_PRIORITY。
OFF

在索引操作期间应用表锁。这样可防止所有用户在操作期间访问基础表。

RESUME

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库

恢复已手动或由于失败而暂停的索引操作。

MAX_DURATION 与 RESUMABLE = ON 一起使用

恢复后执行可恢复联机索引操作的时间（以分钟为单位指定的整数值）。该时间过后，如果可恢复操作仍在运行，则它会暂停。
WAIT_AT_LOW_PRIORITY 与 RESUMABLE = ON 和 ONLINE = ON.

在暂停之后恢复联机索引必须等待对此表执行的阻塞操作。 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 数据库

暂停可恢复联机索引重新生成操作。

ABORT

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库

中止已声明为可恢复的正在运行或已暂停的索引操作。必须显式执行 ABORT 命令才能终止可恢复索引重新生成操作。失败或暂停可恢复索引操作不会终止其执行；而是将操作停留在无限期暂停状态。

备注

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

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

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

在多处理器计算机中，就像其他查询那样，ALTER INDEX REBUILD 自动使用更多处理器来执行与修改索引相关联的扫描和排序操作。运行 ALTER INDEX REORGANIZE 时，无论是否有 LOB_COMPACTION，max degree of parallelism 值均为单个线程化操作。有关详细信息，请参阅配置并行索引操作。

重要

如果索引所在的文件组处于脱机状态或设置为只读，则无法重新组织或重新生成索引。如果指定了关键字 ALL，但有一个或多个索引位于脱机文件组或只读文件组中，该语句将失败。

重建索引

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

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

重新组织索引

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

如果指定了 ALL，将重新组织表中的关系索引（包括聚集索引和非聚集索引）和 XML 索引。指定 ALL 时应用某些限制，请参阅本文“参数”部分的 ALL 定义。

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

重要

对于具有有序聚集列存储索引的 Azure Synapse Analytics 表， ALTER INDEX REORGANIZE 不会对数据重新排序。要对数据重新排序，可使用 ALTER INDEX REBUILD。

禁用索引

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

注意

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

如果表位于事务复制发布中，则无法禁用任何与主键列关联的索引。复制需要使用这些索引。若要禁用索引，必须先从发布中删除该表。有关详细信息，请参阅发布数据和数据库对象。

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

设置选项

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

行锁和页锁选项

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

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

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

选项	详细信息
`ALLOW_ROW_LOCKS = ON or OFF`	应用于堆和任何关联的非聚集索引。
`ALLOW_PAGE_LOCKS = ON`	应用于堆和任何关联的非聚集索引。
`ALLOW_PAGE_LOCKS = OFF`	完全针对非聚集索引。这意味着不允许对非聚集索引使用所有页锁。在堆中，仅不允许使用有页的共享 (S) 锁、更新 (U) 锁和排他 (X) 锁。数据库引擎仍然可以获取意向页锁（IS、IU 或 IX），供内部使用。

联机索引操作

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

重新组织索引始终联机执行。该进程不长期保留锁，因此，不阻塞正在运行的查询或更新。

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

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

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

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

可恢复索引操作

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库

联机索引重新生成可使用 RESUMABLE = ON 选项指定为可恢复。

RESUMABLE 选项对于给定索引在元数据不持久，并且仅适用于当前 DDL 语句的持续时间。因此，必须显式指定 RESUMABLE = ON 子句才能启用可恢复性。
支持对 RESUMABLE = ON 选项或 low_priority_lock_wait 选项使用 MAX_DURATION 选项。
- MAX_DURATION for RESUMABLE 选项指定要重新生成索引的时间间隔。使用此时间后，索引重新生成将暂停或完成其执行。由用户确定何时可以恢复暂停的索引的重新生成。 MAX_DURATION 时间（以分钟为单位）必须大于 0 分钟，且小于等于一周（7 * 24 * 60 = 10080 分钟）。对索引操作进行长时间暂停可能会影响特定表的 DML 性能以及数据库磁盘容量，因为索引（原始索引和新创建的索引）都需要磁盘空间，并且需要在 DML 操作期间更新。如果 MAX_DURATION 省略了选项，索引操作将一直持续到其完成或失败为止。
- 通过 low_priority_lock_wait 参数选项可确定在 Sch-M 锁上阻塞时，索引操作如何才能继续。
如果使用相同的参数重新执行原始 ALTER INDEX REBUILD 语句，会恢复暂停的索引重新生成操作。还可通过执行 ALTER INDEX RESUME 语句来恢复暂停的索引重新生成操作。
可恢复索引不支持 SORT_IN_TEMPDB = ON 选项
具有 RESUMABLE = ON 的 DDL 命令无法在显式事务（不能属于 BEGIN TRAN ... COMMIT 块）中执行。
只有暂停的索引操作才可恢复。
恢复暂停的索引操作时，可以将值更改为 MAXDOP 新值。如果在 MAXDOP 恢复暂停的索引操作时未指定，则采用最后一 MAXDOP 个值。 MAXDOP如果未为索引重新生成操作指定该选项，则采用默认值。
若要立即暂停索引操作，可停止正在进行的命令 (Ctrl-C)，也可执行 ALTER INDEX PAUSE 命令或 KILL <session_id> 命令。命令暂停后，可以使用选项恢复 RESUME 该命令。
ABORT 命令可终止承载原始索引重新生成的会话，并中止索引操作
除了以下情况，可恢复索引重新生成无需额外资源
- 保留要生成的索引所需的额外空间，包括索引暂停的时间
- 阻止任何 DDL 修改的 DDL 状态
虚影清理在索引暂停阶段运行，但在索引运行期间暂停。对于可恢复索引重新生成操作会禁用以下功能
- RESUMABLE = ON 不支持重新生成已禁用的索引
- ALTER INDEX REBUILD ALL 命令
- ALTER TABLE，使用索引重新生成
- 具有 RESUMABLE = ON 的 DDL 命令无法在显式事务（不能属于 BEGIN TRAN ... COMMIT 块）中执行
- 重新生成已计算 TIMESTAMP 或列作为键列的索引。
如果基表包含 LOB 列可恢复聚集索引重新生成，则需要在此操作开始时设置 Sch-M 锁

注意

DDL 命令会运行到完成、暂停或失败。如果命令暂停，将发出一个错误，指示操作已暂停，并且索引创建未完成。可以从 sys.index_resumable_operations 获取有关当前索引状态的详细信息。如同之前一样，发生失败时，也会发出错误。

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

适用于：SQL Server 2014（12.x）及更高版本，以及Azure SQL 数据库

可通过 low_priority_lock_wait 语法指定 WAIT_AT_LOW_PRIORITY 行为。 WAIT_AT_LOW_PRIORITY 只能与 ONLINE = ON 一起使用。

要执行联机索引重新生成的 DDL 语句，必须完成对某一特定表运行的所有活动阻塞事务。在联机索引重新生成执行时，它会阻塞准备对此表执行的所有新事务。尽管联机索引重新生成锁定的持续时间很短，但等待给定表上的所有打开事务完成并阻止新事务启动，可能会显著影响吞吐量，导致工作负荷减慢或超时，并显著限制对基础表的访问。

通过 WAIT_AT_LOW_PRIORITY 选项，DBA 可管理联机索引重新生成需要的架构稳定性 (Sch-S) 锁和架构修改 (Sch-M) 锁，还可在两个选项中二选一。在任一情况下，如果等待期间 (MAX_DURATION = n [minutes]) 没有阻塞活动，则联机索引重新生成会立即执行，而不等待 DDL 语句完成。

WAIT_AT_LOW_PRIORITY 指示联机索引重新生成操作等待低优先级锁，从而允许其他操作在联机索引生成操作等待时继续。省略 WAIT AT LOW PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

MAX_DURATION = 时间 [ 分钟 ]

执行 DDL 命令时，联机索引重新生成锁定等待的等待时间（以分钟为单位指定的整数值）。如果操作在一段时间内被 MAX_DURATION 阻止，则会执行指定的 ABORT_AFTER_WAIT 操作。 MAX_DURATION 时间始终以分钟为单位，可以省略单词 MINUTES 。

ABORT_AFTER_WAIT = [ NONE |标准版LF |阻止程序 ]

NONE

继续以普通（常规）优先级等待锁。
SELF

不采取任何操作，直接退出当前执行的联机索引重新生成 DDL 操作。选项 SELF 不能与 0 一 MAX_DURATION 起使用。
BLOCKERS

终止阻塞联机索引重新生成 DDL 操作的所有用户事务以使操作可以继续。 BLOCKERS 选项需要登录名具有 ALTER ANY CONNECTION 权限。

空间索引限制

重新生成空间索引时，基础用户表在索引操作持续期间不可用，因为空间索引持有架构锁。

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

在单个分区重新生成操作中，无法指定任何空间索引。但是，您可以在完整的分区重新生成过程中指定空间索引。

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

数据压缩

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

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

以下限制适用于已分区索引：

使用 ALTER INDEX ALL ... 时，如果表具有非对齐索引，则无法更改单个分区的压缩设置。
ALTER INDEX <index> ... REBUILD PARTITION ... 语法可重新生成索引的指定分区。
ALTER INDEX <index> ... REBUILD WITH ... 语法可重新生成索引的所有分区。

统计信息

在对表执行 ALTER INDEX ALL ... 时，只更新与索引相关联的统计信息。针对表（而不是索引）自动或手动创建的统计信息不会更新。

权限

若要执行 ALTER INDEX，至少需要对表或视图具有 ALTER 权限。

版本说明

SQL 数据库不使用文件组和文件流选项。
列存储索引在 SQL Server 2012（11.x）之前不可用。
SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库提供可恢复索引操作。

基本语法示例

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

示例：列存储索引

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

A. REORGANIZE 演示

此示例演示 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 行组时非常有用。

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

此示例 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（从 SQL Server 2016 (13.x) 开始）和 Azure SQL 数据库

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

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

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

此示例将所有 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 行。更改的所有行组都会重新压缩。

注意

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

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

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

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

适用对象：SQL Server（从 SQL Server 2012 (11.x) 开始）

提示

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

注意

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

此示例演示如何重新生成聚集列存储索引以及将所有增量行组都强制到列存储中。这是第一步，将准备具有一个聚集列存储索引的 AdventureWorksDW2022 数据库中的表 FactInternetSales2并插入来自前 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) 及更高版本

若要重新生成大型聚集列存储索引的分区，请使用带有分区选项的 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. 重新生成索引

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

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

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

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 数据库

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 压缩重新组织索引

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

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

D. 设置索引上的选项

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

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

E. 禁用索引

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

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 禁用约束

以下示例通过禁用数据库中的PRIMARY KEY索引AdventureWorks2022来禁用PRIMARY KEY约束。基础 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. 重新生成已分区索引

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

适用于：SQL Server 2014（12.x）及更高版本，以及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 数据库

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

使用 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;

Share via

ALTER INDEX (Transact-SQL)

语法

参数

index_name

ALL

database_name

schema_name

table_or_view_name

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

PARTITION

DISABLE

对行存储索引执行 REORGANIZE

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

对列存储索引执行 REORGANIZE

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

标准版T （<set_index 选项> [ ,... n ]

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 | OFF } <同样适用于 rebuild_index_option>

RESUMABLE = { ON | OFF}

MAX_DURATION = 时间 [ 分钟] 用于 RESUMABLE = ON （需要 ONLINE = ON）

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

DATA_COMPRESSION

XML_COMPRESSION

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

ONLINE = { ON | OFF } <同样适用于 single_partition_rebuild_index_option>

RESUME

PAUSE

ABORT

备注

重建索引

重新组织索引

禁用索引

设置选项

行锁和页锁选项

联机索引操作

可恢复索引操作

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

MAX_DURATION = 时间 [ 分钟 ]

ABORT_AFTER_WAIT = [ NONE |标准版LF |阻止程序 ]

空间索引限制

数据压缩

统计信息

权限

版本说明

基本语法示例

示例：列存储索引

A. REORGANIZE 演示

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

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

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

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

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

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

示例：行存储索引

A. 重新生成索引

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

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

D. 设置索引上的选项

E. 禁用索引

F. 禁用约束

G. 启用约束

H. 重新生成已分区索引

I. 更改索引的压缩设置

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

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

相关内容

反馈

反馈

其他资源

MAX_DURATION = 时间 [ 分钟] 用于 `RESUMABLE = ON` （需要 `ONLINE = ON`）