ALTER INDEX (Transact-SQL)

  • 项目

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

适用于：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

主题链接图标 Transact-SQL 语法约定

语法

-- SQL Server Syntax

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 ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_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 } 
  | 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_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

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

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

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

-- Windows Azure SQL Database Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 

        ] 
    | DISABLE
    | SET ( <set_index_option> [ ,...n ] ) 
    }
 [ ; ] 

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

<rebuild_index_option > ::= 
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
}

<set_index_option>::=
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

参数

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

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

    使用此操作指定 ALL

    如果表有一个或多个，语句会失败

    REBUILD WITH ONLINE = ON

    XML 索引

    空间索引

    列存储索引

    适用于：SQL Server 2012 到 SQL Server 2014。

    REBUILD PARTITION = partition_number

    未分区的索引、XML 索引、空间索引或已禁用的索引

    REORGANIZE

    ALLOW_PAGE_LOCKS 设置为 OFF 的索引。

    REORGANIZE PARTITION = partition_number

    未分区的索引、XML 索引、空间索引或已禁用的索引

    IGNORE_DUP_KEY = ON

    XML 索引

    空间索引

    列存储索引

    适用于：SQL Server 2012 到 SQL Server 2014。

    ONLINE = ON

    XML 索引

    空间索引

    列存储索引

    适用于：SQL Server 2012 到 SQL Server 2014。

    备注

    有关可以联机执行的索引操作的更详细信息，请参阅联机索引操作准则。

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

  • database_name
    数据库的名称。

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

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

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

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

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

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

    备注

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

  • PARTITION

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

    备注

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

  • partition_number

    适用于：SQL Server 2008 到 SQL Server 2014。

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

  • WITH (<single_partition_rebuild_index_option>)

    适用于：SQL Server 2008 到 SQL Server 2014。

    SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是在重新生成单个分区 (PARTITION = n) 时可以指定的选项。 不能在单个分区重新生成操作中指定 XML 索引。

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

  • REORGANIZE
    指定将重新组织的索引叶级。 对于聚集列存储索引，它指定所有关闭的行组都将移入列存储中。 ALTER INDEX REORGANIZE 语句始终联机执行。 这意味着不保留长期阻塞的表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续。 不能为已禁用的索引或 ALLOW_PAGE_LOCKS 设置为 OFF 的索引指定 REORGANIZE。

  • WITH ( LOB_COMPACTION = { ON | OFF } )

    适用于：SQL Server 2008 到 SQL Server 2014。

    指定压缩所有包含大型对象 (LOB) 数据的页。 LOB 数据类型包括 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。 压缩此数据可以改善磁盘空间使用情况。 默认值为 ON。

    • ON
      压缩所有包含大型对象数据的页。

      重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。

      重新组织非聚集索引将压缩作为索引中非键（已包括）列的所有 LOB 列。指定 ALL 时，将重新组织与指定表或视图相关联的所有索引，并且压缩与聚集索引、基础表或具有包含列的非聚集索引相关联的所有 LOB 列。

    • OFF
      不压缩包含大型对象数据的页。

      OFF 对堆没有影响。

    如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。

  • SET ( <set_index option> [ ,...n] )
    指定不重新生成或重新组织索引的索引选项。 不能为已禁用的索引指定 SET。

  • PAD_INDEX = { ON | OFF }

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

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

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

  • FILLFACTOR = fillfactor

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

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

    重要说明重要提示

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

  • SORT_IN_TEMPDB = { ON | OFF }

    适用于：SQL Server 2008 到 SQL Server 2014。

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

    • 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 }
    为 ON 时，根据分区统计信息创建统计信息。 为 OFF 时，删除统计信息树并且 SQL Server 重新计算统计信息。 默认值为 OFF。

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

    • 使用未与基表的分区对齐的索引创建的统计信息。

    • 对 AlwaysOn 可读辅助数据库创建的统计信息。

    • 对只读数据库创建的统计信息。

    • 对筛选的索引创建的统计信息。

    • 对视图创建的统计信息。

    • 对内部表创建的统计信息。

    • 使用空间索引或 XML 索引创建的统计信息。

    适用于：SQL Server 2014 到 SQL Server 2014。

  • ONLINE = { ON | OFF } <同样适用于 rebuild_index_option>
    指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认值为 OFF。

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

    备注

    在 Microsoft SQL Server 的每个版本中均不支持联机索引操作。有关 SQL Server 的每个版本支持的功能列表，请参阅 SQL Server 2014 各个版本支持的功能。

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

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

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

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

    • XML 索引

    • 本地临时表中的索引

    • 分区索引的子集（可以联机重新构建整个分区索引。）

    如果表包含 LOB 数据类型，但这些列中没有任何列在索引定义中用作键列或非键列，则可以联机重新生成非聚集索引。

  • ALLOW_ROW_LOCKS = { ON | OFF }

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

    • OFF
      不使用行锁。

  • ALLOW_PAGE_LOCKS = { ON | OFF }

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

    • OFF
      不使用页锁。

    备注

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

  • MAXDOP **=**max_degree_of_parallelism

    适用于：SQL Server 2008 到 SQL Server 2014。

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

    重要说明重要提示

    虽然所有 XML 索引在语法上都支持 MAXDOP 选项，但对于空间索引或主 XML 索引，ALTER INDEX 当前只使用一个处理器。

    max_degree_of_parallelism 可以是：

    • 1
      取消生成并行计划。

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

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

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

    备注

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

  • DATA_COMPRESSION

    适用于：SQL Server 2008 到 SQL Server 2014。

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

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

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

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

    • COLUMNSTORE

      适用于：SQL Server 2014 到 SQL Server 2014。

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

    • COLUMNSTORE_ARCHIVE

      适用于：SQL Server 2014 到 SQL Server 2014。

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

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

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

    适用于：SQL Server 2008 到 SQL Server 2014。

    指定对其应用 DATA_COMPRESSION 设置的分区。 如果索引未分区，则 ON PARTITIONS 参数将产生错误。 如果不提供 ON PARTITIONS 子句，则 DATA_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)
);

  • ONLINE = { ON | OFF } <同样适用于 single_partition_rebuild_index_option>
    指定索引或基础表的索引分区是否可以联机或脱机重新生成。 如果 REBUILD 联机执行 (ON)，则索引操作期间可以用此表中的数据进行查询和修改数据。 默认值为 OFF。

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

      备注

      联机索引重新生成可以设置本节稍后介绍的 low_priority_lock_wait 选项。

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

  • WAIT_AT_LOW_PRIORITY

    适用于：SQL Server 2014 到 SQL Server 2014。

    联机索引重新生成必须等待对此表执行的阻塞操作。 WAIT_AT_LOW_PRIORITY 表示联机索引重新生成操作将等待低优先级锁，从而允许其他操作在该联机索引生成操作正在等待时继续进行。 省略 WAIT AT LOW PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

  • MAX_DURATION = time [MINUTES ]

    适用于：SQL Server 2014 到 SQL Server 2014。

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

  • ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

    适用于：SQL Server 2014 到 SQL Server 2014。

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

    • SELF
      不采取任何操作，直接退出当前执行的联机索引重新生成 DDL 操作。

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

注释

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

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

备注

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

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

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

重新生成索引

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

重新生成或重新组织小索引不会减少碎片。 小索引的页面存储在混合区中。 混合区最多可由八个对象共享，因此在重新组织或重新生成小索引之后可能不会减少小索引中的碎片。

在 SQL Server 2014 中，当创建或重新生成已分区索引时，将通过扫描表中的所有行来创建统计信息。 相反，查询优化器使用默认采样算法来生成统计信息。 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息，请使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。

在早期版本的 SQL Server 中，您有时可以重新生成非聚集索引以纠正硬件故障导致的不一致问题。 在 SQL Server 2008 和更高版本中，您仍然可以通过脱机重新生成非聚集索引来纠正索引和聚集索引之间的这种不一致问题。 但是，您不能通过联机重新生成索引来纠正非聚集索引的不一致，因为联机重新生成机制将会使用现有的非聚集索引作为重新生成的基础，因此仍存在不一致。 相反，脱机重新生成索引将会强制扫描聚集索引（或堆），因此会删除不一致。 与早期版本一样，建议通过从备份还原受影响的数据来从不一致状态进行恢复；但是，您可以通过脱机重新生成非聚集索引来纠正索引的不一致。 有关详细信息，请参阅 DBCC CHECKDB (Transact-SQL)。

若要重新生成聚集列存储索引，SQL Server 将：

  1. 在重新生成进行时获取表或分区上的排他锁。 在重新生成期间数据“处于脱机状态”并且不可用。

  2. 通过物理删除已从表中逻辑上删除的行对列存储进行碎片整理；已删除的字节在物理介质上回收。

  3. 从原始列存储索引（包括增量存储）中读取所有数据。 它将数据合并到新的行组中，并且将行组压缩到列存储中。

  4. 要求物理介质上的空间，以便在进行重新生成时存储列存储索引的两个副本。 在重新生成完成后，SQL Server 将删除原始聚集列存储索引。

重新组织索引

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

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

若要对某一聚集列存储索引进行重新组织，SQL Server 会将标记为 CLOSED 的所有行组移入该列存储中。重新组织并不是将 CLOSED 行组移入列存储所必需的。 元组搬运者进程最终将找到所有关闭的行组并且移动它们。 但是，tuple-mover 是单线程的，因此，对于您的工作负荷来说它移动行组的速度可能不够快。 为了确保在关闭行组后移动它们，您可以在每次加载后运行 ALTER INDEX REORGANIZE。

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

禁用索引

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

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

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

设置选项

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

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

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

  • 创建多个非聚集索引。

  • 在同一个表中重新组织不同索引。

  • 在同一个表中重新生成不重叠的索引时，重新组织不同的索引。

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

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

WAIT_AT_LOW_PRIORITY

要执行联机索引重新生成的 DDL 语句，必须完成对某一特定表运行的所有活动阻塞事务。 在联机索引重新生成执行时，它会阻塞准备对此表执行的所有新事务。 尽管联机索引重新生成锁的持续时间非常短，但等待某一给定表的所有打开的事务完成并阻塞新事务启动可能对吞吐量造成很大影响，导致工作负荷变慢或超时，并严重限制对基础表的访问。 WAIT_AT_LOW_PRIORITY 选项允许 DBA 管理联机索引重新生成需要的 S 锁和 Sch-M 锁，并允许他们选择 3 个选项之一。 在所有 3 种情况下，如果等待期间 ( (MAX_DURATION = n [minutes]) ) 没有阻塞活动，则联机索引重新生成会立即执行，而不等待 DDL 语句完成。

空间索引限制

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

对用户表的某一列定义了空间索引时，无法修改该表中的 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 权限。

示例

A.重新生成索引

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

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

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

适用于：SQL Server 2008 到 SQL Server 2014。

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

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

适用于：SQL Server 2014 到 SQL Server 2014。

 
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.重新生成聚集列存储索引

这个第一步将准备具有一个聚集列存储索引的表 FactInternetSales2 并且插入来自前四列的数据。

USE AdventureWorksDW2012;
GO
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)，这意味着行组的列段现在被压缩并且存储于列存储中。

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

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

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;

E.设置索引的选项。

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

适用于：SQL Server 2008 到 SQL Server 2014。

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

F.禁用索引。

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

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;

G.禁用约束

下面的示例通过禁用 AdventureWorks2012 数据库中的 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'.

H.启用约束

下面的示例启用在示例 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

I.重新生成分区索引

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

适用于：SQL Server 2014 到 SQL Server 2014。

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

J.更改索引的压缩设置

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

适用于：SQL Server 2008 到 SQL Server 2014。

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

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

适用于：SQL Server 2014 到 SQL Server 2014。

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

--Remove the archive compression and only use columnstore compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE );
GO

有关其他数据压缩示例，请参阅数据压缩。

请参阅

参考

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

概念

禁用索引和约束

XML 索引 (SQL Server)

联机执行索引操作

重新组织和重新生成索引