ALTER INDEX (Transact-SQL)

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

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

Transact-SQL 语法约定

语法

-- Syntax for SQL Server and Azure SQL Database

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

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

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

自变量

index_name

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

ALL

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

将关键字 ALL 与此操作一起使用	如果表有一个或多个，语句会失败
REBUILD WITH ONLINE = ON	XML 索引 空间索引 列存储索引：适用于： SQL Server（从 SQL Server 2012 (11.x) 开始）和 Azure SQL 数据库
REBUILD PARTITION = partition_number	未分区的索引、XML 索引、空间索引或已禁用的索引
REORGANIZE	ALLOW_PAGE_LOCKS 设置为 OFF 的索引。
REORGANIZE PARTITION = partition_number	未分区的索引、XML 索引、空间索引或已禁用的索引
IGNORE_DUP_KEY = ON	XML 索引 空间索引 列存储索引：适用于： SQL Server（从 SQL Server 2012 (11.x) 开始）和 Azure SQL 数据库
ONLINE = ON	XML 索引 空间索引 列存储索引：适用于： SQL Server（从 SQL Server 2012 (11.x) 开始）和 Azure SQL 数据库
RESUMABLE = ON	All 关键字不支持可恢复索引。 适用对象：SQL Server（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

如果将 PARTITION = partition_number 与 ALL 一起指定，则必须对齐所有索引。 这意味着，它们是基于等同的分区函数进行分区的。 将 ALL 与 PARTITION 一起使用可导致重新生成或重新组织所有具有相同 partition_number 的索引分区。 有关已分区索引的详细信息，请参阅 Partitioned Tables and Indexes。

database_name

数据库的名称。

schema_name

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

table_or_view_name

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

SQL 数据库支持由三部分组成的名称格式 database_name.[schema_name].table_or_view_name，其中 database_name 为当前数据库，或 database_name 为 tempdb，table_or_view_name 以 # 开头。

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

适用对象：SQL Server（从 SQL Server 2012 (11.x) 开始）和 Azure SQL 数据库

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

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

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

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

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

有关详细信息，请参阅 重新组织和重新生成索引。

PARTITION

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

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

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 事务处理期间继续进行。
• 不允许用于禁用的索引。
• 在 ALLOW_PAGE_LOCKS 设置为 OFF 时不允许执行。
• 当在事务中执行而事务回滚时不会回滚。

有关详细信息，请参阅 重新组织和重新生成索引。

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 即可将关闭的增量行组移动到压缩行组中。 后台 tuple-mover (TM) 进程会定期唤醒以压缩 CLOSED 增量行组。 建议在 tuple-mover 落后时使用 REORGANIZE。 REORGANIZE 可以更主动地压缩行组。
• 若要压缩所有 OPEN 和 CLOSED 行组，请参阅本部分中的 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 选项。

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

• 在逻辑删除了 10% 或更多行时从行组中物理移除行。 删除的字节会在物理媒体上进行回收。 例如，如果具有 100 万行的压缩行组删除了 10 万行，则 SQL Server 会移除已删除的行，并使用 90 万行重新压缩行组。 它通过移除已删除的行来节省存储。

• 合并一个或多个压缩行组，以将每个行组的行增加到最多 1,048,576 行。 例如，如果批量导入 5 批 102,400 行，则会获得 5 个压缩行组。 如果运行 REORGANIZE，则这些行组会合并为 1 个大小为 512,000 的压缩行组。 这假定不存在任何字典大小或内存限制。

• 对于在其中已逻辑删除了 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 提供将打开或关闭的增量行组强制到列存储中的方式。 使用此选项时，无需重新生成列存储索引即可清空增量行组。 此操作与其他移除和合并碎片整理功能相结合，使得在大多数情况下不再需要重新生成索引。

• ON 将所有行组都强制到列存储中，而不考虑大小和状态（关闭或打开）。
• OFF 将所有关闭的行组强制到列存储中。

有关详细信息，请参阅 重新组织和重新生成索引。

SET ( <set_index option> [ ,... n] )

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

PAD_INDEX = { ON | OFF }

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

ON

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

OFF 或未指定 fillfactor

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

有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。

FILLFACTOR = fillfactor

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

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

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

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 操作将被回滚。

对于针对视图创建的索引、非唯一索引、XML 索引、空间索引以及筛选的索引，IGNORE_DUP_KEY 不能设置为 ON。

若要查看 IGNORE_DUP_KEY，请使用 sys.indexes。

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

STATISTICS_NORECOMPUTE = { ON | OFF}

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

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

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

适用对象：SQL Server（从 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，便会引发错误。

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

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

有关详细信息，请参阅 Perform Index Operations Online。

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

• 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（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

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

ON
索引操作可恢复。

OFF
索引操作不可恢复。

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

适用对象：SQL Server（从 SQL Server 2017 (14.x) 开始）和 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（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

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

MAXDOP = max_degree_of_parallelism

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

max_degree_of_parallelism 可以是：

1
取消生成并行计划。

>1
将并行索引操作中使用的最大处理器数量限制为指定数量。

0（默认值）
根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。

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

COMPRESSION_DELAY = { 0 | duration [Minutes] }

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

对于基于磁盘的表，延迟指定增量行组中处于关闭状态的增量行组在 SQL Server 可以将它压缩为压缩行组之前必须保持为增量行组的最小分钟数。 由于基于磁盘的表不对单个行跟踪插入和更新时间，因此 SQL Server 会将此延迟应用于处于关闭状态的增量行组。

默认为 0 分钟。

有关何时使用 COMPRESSION_DELAY 的建议，请参阅开始使用列存储进行实时运行分析。

DATA_COMPRESSION

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

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

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

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

COLUMNSTORE

适用对象：SQL Server（从 SQL Server 2014 (12.x) 开始）和 Azure SQL 数据库

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

COLUMNSTORE_ARCHIVE

适用对象：SQL Server（从 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 锁在访问同一表时阻止在此锁后等待的所有其他事务。

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

RESUME

适用对象：SQL Server（从 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（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

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

ABORT

适用对象：SQL Server（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

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

备注

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

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

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

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

重新生成索引

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

有关详细信息，请参阅 重新组织和重新生成索引。

重新组织索引

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

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

有关详细信息，请参阅 重新组织和重新生成索引。

禁用索引

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

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

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

设置选项

可以为指定索引设置选项 ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKS、OPTIMIZE_FOR_SEQUENTIAL_KEY、IGNORE_DUP_KEY 和 STATISTICS_NORECOMPUTE，而无需重新生成或重新组织相应索引。 修改的值立即应用于索引。 若要查看这些设置，请使用 sys.indexes。 有关详细信息，请参阅 设置索引选项。

行锁和页锁选项

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

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

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

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

联机索引操作

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

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

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

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

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

有关详细信息，请参阅 Perform Index Operations Online。

可恢复索引操作

适用对象：SQL Server（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

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

• RESUMABLE 选项对于给定索引在元数据不持久，并且仅适用于当前 DDL 语句的持续时间。 因此，必须显式指定 RESUMABLE = ON 子句才能启用可恢复性。

• 支持对 RESUMABLE = ON 选项或 low_priority_lock_wait 选项使用 MAX_DURATION 选项。

  • 用于 RESUMABLE 选项的 MAX_DURATION 为重新生成的索引指定时间间隔。 使用此时间之后，索引重新生成会暂停或完成其执行。 由用户确定何时可以恢复暂停的索引的重新生成。 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 锁

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

适用对象：SQL Server（从 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 = time [MINUTES]

联机索引重新生成锁将在执行 DDL 命令时以低优先级等待的等待时间（以分钟为单位指定的整数值）。 如果操作被阻塞的时间达到 MAX_DURATION，将执行指定的 ABORT_AFTER_WAIT 操作。 MAX_DURATION 时间始终以分钟为单位，MINUTES 一词可以省略。

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

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

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

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

空间索引限制

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

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

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

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

数据压缩

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

若要评估更改 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 TRAN
    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;

运行此命令可查看打开的增量行组。 行组数取决于并行度。

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

运行此命令以将所有关闭和打开的行组强制到列存储中。

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 增量行组作为压缩行组压缩到列存储中。 这不是必需的，但是在 tuple-mover 压缩关闭的行组的速度不够快时非常有用。

可在 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 的行组。 因此，在压缩所有打开和关闭的行组时，最后不会得到与大量其中只包含少量行的压缩行组。 需要行组尽可能满，以减少压缩大小并提高查询性能。

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

此示例会将所有打开和关闭的增量行组压缩到列存储索引中。

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

此示例会将所有打开和关闭的增量行组压缩到特定分区的列存储索引中。

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

以下示例执行 REORGANIZE，以物理方式删除已从表中逻辑删除的行且合并行组来对索引进行碎片整理。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

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

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

此示例演示如何重新生成聚集列存储索引以及将所有增量行组都强制到列存储中。 这是第一步，将准备具有一个聚集列存储索引的 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;

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

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

SELECT 语句的结果表明行组已压缩，这意味着行组的列段现在已压缩且存储在列存储中。

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

适用于：SQL Server（从 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. 重新生成表的所有索引并指定选项

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

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

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

适用对象：SQL Server（从 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. 禁用约束

下面的示例通过禁用 AdventureWorks2022 数据库中的 PRIMARY KEY 索引来禁用 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（从 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（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

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

1. 作为 MAXDOP = 1 的可恢复操作执行联机索引重新生成。

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

2. 在索引操作暂停之后再次执行相同命令（请参阅上文）会自动恢复索引重新生成操作。

3. 作为 MAX_DURATION 设置为 240 分钟的可恢复操作执行联机索引重新生成。

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

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

   ALTER INDEX test_idx on test_table PAUSE ;
   

5. 对作为可恢复操作（为设置为 4 的 MAXDOP 指定新值）执行的索引重新生成，恢复联机索引重新生成。

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

6. 对作为可恢复操作执行的索引联机重新生成恢复联机索引重新生成操作。 将 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)) ;
   

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

   ALTER INDEX test_idx on test_table ABORT ;
   

另请参阅

• SQL Server 索引体系结构和设计指南
• 联机执行索引操作
• CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• 禁用索引和约束
• XML 索引 (SQL Server)
• 重新组织和重新生成索引
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)