ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

通过禁用、重新生成或重新组织索引或者设置索引的选项,修改现有的表或视图索引(关系或 XML)。Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- 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_options>,[...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  
    | COMPRESSION_DELAY = {0 | delay [Minutes]}  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }   
        [ 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} }  
    | 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 SQL Data Warehouse 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 ] ) ]   
}

<single_partition_rebuild_index_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
}  
  

参数Arguments

index_name index_name
索引的名称。Is the name of the index. 索引名称在表或视图中必须唯一,但在数据库中不必唯一。Index names must be unique within a table or view but do not have to be unique within a database. 索引名称必须符合标识符的规则。Index names must follow the rules of identifiers.

ALLALL
指定与表或视图相关联的所有索引,而不考虑是什么索引类型。Specifies all indexes associated with the table or view regardless of the index type. 如果有一个或多个索引脱机或不允许对一个或多个索引类型执行只读文件组操作或指定操作,则指定 ALL 将导致语句失败。Specifying ALL causes the statement to fail if one or more indexes are in an offline or read-only filegroup or the specified operation is not allowed on one or more index types. 下表列出了索引操作和不允许使用的索引类型。The following table lists the index operations and disallowed index types.

将关键字 ALL 与此操作一起使用Using the keyword ALL with this operation 如果表有一个或多个,语句会失败Fails if the table has one or more
REBUILD WITH ONLINE = ONREBUILD WITH ONLINE = ON XML 索引XML index

空间索引Spatial index

列存储索引:适用范围: SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)和 SQL 数据库SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and SQL 数据库SQL Database.
REBUILD PARTITION = partition_numberREBUILD PARTITION = partition_number 未分区的索引、XML 索引、空间索引或已禁用的索引Nonpartitioned index, XML index, spatial index, or disabled index
REORGANIZEREORGANIZE ALLOW_PAGE_LOCKS 设置为 OFF 的索引。Indexes with ALLOW_PAGE_LOCKS set to OFF
REORGANIZE PARTITION = partition_numberREORGANIZE PARTITION = partition_number 未分区的索引、XML 索引、空间索引或已禁用的索引Nonpartitioned index, XML index, spatial index, or disabled index
IGNORE_DUP_KEY = ONIGNORE_DUP_KEY = ON XML 索引XML index

空间索引Spatial index

列存储索引:适用范围: SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)和 SQL 数据库SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and SQL 数据库SQL Database.
ONLINE = ONONLINE = ON XML 索引XML index

空间索引Spatial index

列存储索引:适用范围: SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)和 SQL 数据库SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and SQL 数据库SQL Database.
RESUMABLE = ONRESUMABLE = ON All 关键字不支持可恢复索引。Resumable indexes not supported with All keyword.

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

警告

有关可以联机执行的索引操作的更详细信息,请参阅联机索引操作准则For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

如果将 PARTITION = partition_number 与 ALL 一起指定,则必须对齐所有索引。If ALL is specified with PARTITION = partition_number, all indexes must be aligned. 这意味着,它们是基于等同的分区函数进行分区的。This means that they are partitioned based on equivalent partition functions. 将 ALL 与 PARTITION 一起使用可导致重新生成或重新组织所有具有相同 partition_number 的索引分区。Using ALL with PARTITION causes all index partitions with the same partition_number to be rebuilt or reorganized. 有关已分区索引的详细信息,请参阅 Partitioned Tables and IndexesFor more information about partitioned indexes, see Partitioned Tables and Indexes.

database_namedatabase_name
数据库的名称。Is the name of the database.

schema_nameschema_name
表或视图所属架构的名称。Is the name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
与该索引关联的表或视图的名称。Is the name of the table or view associated with the index. 若要显示对象的索引报表,请使用 sys.indexes 目录视图。To display a report of the indexes on an object, use the sys.indexes catalog view.

SQL 数据库SQL Database支持由三部分组成的名称格式 database_name.[schema_name].table_or_view_name,其中 database_name 为当前数据库,或 database_name 为 tempdb,table_or_view_name 以 # 开头。supports the three-part name format database_name.[schema_name].table_or_view_name when the database_name is the current database or the database_name is tempdb and the table_or_view_name starts with #.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ] REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引。Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. 此子句等同于 DBCC DBREINDEXThis clause is equivalent to DBCC DBREINDEX. REBUILD 启用已禁用的索引。REBUILD enables a disabled index. 重新生成聚集索引并不重新生成关联的非聚集索引,除非指定了关键字 ALL。Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. 如果未指定索引选项,则应用存储在 sys.indexes 中的现有索引选项值。If index options are not specified, the existing index option values stored in sys.indexes are applied. 对于未在 sys.indexes 中存储值的任何索引选项,应用该选项的参数定义中指示的默认值。For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

如果指定 ALL 且基础表为堆,则重新生成操作对表没有任何影响。If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on the table. 重新生成与表相关联的所有非聚集索引。Any nonclustered indexes associated with the table are rebuilt.

如果数据库恢复模式设置为大容量日志记录或简单日志记录,则可以对重新生成操作进行最小日志记录。The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

备注

重新生成主 XML 索引时,基础用户表在索引操作持续期间不可用。When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the index operation.

适用对象SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and SQL 数据库SQL Database.

对于列存储索引,重新生成操作:For columnstore indexes, the rebuild operation:

  1. 不使用排序顺序。Does not use the sort order.

  2. 在重新生成进行时获取表或分区上的排他锁。Acquires an exclusive lock on the table or partition while the rebuild occurs. 数据是“处于脱机状态”,在重新生成期间不可用,即便使用 NOLOCK、RCSI 或 SI 也是如此。The data is "offline" and unavailable during the rebuild, even when using NOLOCK, RCSI, or SI.

  3. 将所有数据重新压缩到列存储中。Re-compresses all data into the columnstore. 在进行重新生成时存在列存储索引的两个副本。Two copies of the columnstore index exist while the rebuild is taking place. 在重新生成完成后, SQL ServerSQL Server 将删除原始列存储索引。When the rebuild is finished, SQL ServerSQL Server deletes the original columnstore index.

有关重新生成列存储索引的详细信息,请参阅列存储索引 - 碎片整理For more information about rebuilding columnstore indexes, see Columnstore indexes - defragmentation

PARTITIONPARTITION

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定只重新生成或重新组织索引的一个分区。Specifies that only one partition of an index will be rebuilt or reorganized. 如果 index_name 不是已分区索引,则不能指定 PARTITION。PARTITION cannot be specified if index_name is not a partitioned index.

PARTITION = ALL 重新生成所有分区。PARTITION = ALL rebuilds all partitions.

警告

对超过 1,000 个分区的表创建和重新生成非对齐索引是可能的,但不支持。Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. 这样做可能会导致性能下降,或在执行这些操作的过程中占用过多内存。Doing so may cause degraded performance or excessive memory consumption during these operations. 我们建议当分区数超过 1000 时,仅使用对齐索引。We recommend using only aligned indexes when the number of partitions exceed 1,000.

partition_numberpartition_number

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

要重新生成或重新组织已分区索引的分区数。Is the partition number of a partitioned index that is to be rebuilt or reorganized. partition_number 是可以引用变量的常量表达式。partition_number is a constant expression that can reference variables. 其中包括用户定义类型变量或函数以及用户定义函数,但不能引用 Transact-SQLTransact-SQL 语句。These include user-defined type variables or functions and user-defined functions, but cannot reference a Transact-SQLTransact-SQL statement. partition_number 必须存在,否则,该语句将失败。partition_number must exist or the statement fails.

WITH ( <single_partition_rebuild_index_option> )WITH (<single_partition_rebuild_index_option>)

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是在重新生成单个分区 (PARTITION = n) 时可以指定的选项 。SORT_IN_TEMPDB, MAXDOP, and DATA_COMPRESSION are the options that can be specified when you rebuild a single partition (PARTITION = n). 不能在单个分区重新生成操作中指定 XML 索引。XML indexes cannot be specified in a single partition rebuild operation.

DISABLEDISABLE
将索引标记为已禁用,从而不能由 数据库引擎Database Engine使用。Marks the index as disabled and unavailable for use by the 数据库引擎Database Engine. 可禁用任何索引。Any index can be disabled. 已禁用的索引的索引定义保留在没有基础索引数据的系统目录中。The index definition of a disabled index remains in the system catalog with no underlying index data. 禁用聚集索引将阻止用户访问基础表数据。Disabling a clustered index prevents user access to the underlying table data. 若要启用索引,请使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. 有关详细信息,请参阅 禁用索引和约束启用索引和约束For more information, see Disable Indexes and Constraints and Enable Indexes and Constraints.

对行存储索引执行 REORGANIZEREORGANIZE a rowstore index
对于行存储索引,REORGANIZE 指定要重新组织索引叶级别。For rowstore indexes, REORGANIZE specifies to reorganize the index leaf level. REORGANIZE 操作:The REORGANIZE operation is:

  • 始终联机执行。Always performed online. 这意味着不保留长期阻塞的表锁,且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续。This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
  • 不允许用于禁用的索引Not allowed for a disabled index
  • 在 ALLOW_PAGE_LOCKS 设置为 OFF 时不允许执行Not allowed when ALLOW_PAGE_LOCKS is set to OFF
  • 当在事务中执行而事务回滚时不会回滚。Not rolled back when it is performed within a transaction and the transaction is rolled back.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )
适用于行存储索引。Applies to rowstore indexes.

LOB_COMPACTION = ONLOB_COMPACTION = ON

  • 指定要压缩包含以下这些大型对象 (LOB) 数据类型的数据的所有页面:图像、文本、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。Specifies to compact all pages that contain data of these large object (LOB) data types: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. 压缩这些数据可以减少磁盘上的数据大小。Compacting this data can reduce the data size on disk.

  • 对于聚集索引,这会压缩表中包含的所有 LOB 列。For a clustered index, this compacts all LOB columns that are contained in the table.

  • 对于非聚集索引,这会压缩作为索引中非键(已包括)列的所有 LOB 列。For a nonclustered index, this compacts all LOB columns that are nonkey (included) columns in the index.

  • REORGANIZE ALL 对所有索引执行 LOB_COMPACTION。REORGANIZE ALL performs LOB_COMPACTION on all indexes. 对于每个索引,这会压缩聚集索引、基础表中的所有 LOB 列 或是非聚集索引中的包含列。For each index, this compacts all LOB columns in the clustered index, underlying table, or included columns in a nonclustered index.

LOB_COMPACTION = OFFLOB_COMPACTION = OFF

  • 不压缩包含大型对象数据的页。Pages that contain large object data are not compacted.

  • OFF 对堆没有影响。OFF has no effect on a heap.

对列存储索引执行 REORGANIZEREORGANIZE a columnstore index
对于列存储索引,REORGANIZE 会将每个关闭的增量行组作为压缩行组压缩到列存储中。For columnstore indexes, REORGANIZE compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. 始终联机执行 REORGANIZE 操作。The REORGANIZE operation is always performed online. 这意味着不保留长期阻塞的表锁,且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续。This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

  • 无需 REORGANIZE 即可将关闭的增量行组移动到压缩行组中。REORGANIZE is not required in order to move CLOSED delta rowgroups into compressed rowgroups. 后台 tuple-mover (TM) 进程会定期唤醒以压缩关闭的增量行组。The background tuple-mover (TM) process wakes up periodically to compress CLOSED delta rowgroups. 建议在 tuple-mover 落后时使用 REORGANIZE。We recommend using REORGANIZE when tuple-mover is falling behind. REORGANIZE 可以更主动地压缩行组。REORGANIZE can compress rowgroups more aggressively.

  • 若要压缩所有打开和关闭的行组,请参阅本部分中的 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 选项。To compress all OPEN and CLOSED rowgroups, see the REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) option in this section.

对于 SQL ServerSQL Server(从 2016 开始)和 SQL 数据库SQL Database中的列存储索引,REORGANIZE 会联机执行以下附加碎片整理优化:For columnstore indexes in SQL ServerSQL Server (Starting with 2016) and SQL 数据库SQL Database, REORGANIZE performs the following additional defragmentation optimizations online:

  • 在逻辑删除了 10% 或更多行时从行组中物理移除行。Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. 删除的字节会在物理媒体上进行回收。The deleted bytes are reclaimed on the physical media. 例如,如果具有 100 万行的压缩行组删除了 10 万行,则 SQL Server 会移除已删除的行,并使用 90 万行重新压缩行组。For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. 它通过移除已删除的行来节省存储。It saves on the storage by removing deleted rows.

  • 合并一个或多个压缩行组以将每个行组的行增加到最多为 1,024,576 行。Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,024,576 rows. 例如,如果批量导入 5 批 102,400 行,则会获得 5 个压缩行组。For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. 如果运行 REORGANIZE,则这些行组会合并为 1 个大小为 512,000 的压缩行组。If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. 这假定不存在任何字典大小或内存限制。This assumes there were no dictionary size or memory limitations.

  • 对于在其中已逻辑删除了 10% 或更多行的行组,SQL Server 会尝试将此行组与一个或多个行组合并。For rowgroups in which 10% or more of the rows have been logically deleted, SQL Server will try to combine this rowgroup with one or more rowgroups. 例如,行组 1 使用 500,000 行进行压缩,行组 21 使用最大值 1,048,576 行进行压缩。For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. 行组 21 删除了 60% 的行,剩下 409,830 行。Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. SQL Server 会优先合并这两个行组以压缩具有 909,830 行的新行组。SQL Server favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

适用范围: SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and SQL 数据库SQL Database

COMPRESS_ALL_ROW_GROUPS 提供将打开或关闭的增量行组强制到列存储中的方式。COMPRESS_ALL_ROW_GROUPS provides a way to force OPEN or CLOSED delta rowgroups into the columnstore. 使用此选项时,无需重新生成列存储索引即可清空增量行组。With this option, it is not necessary to rebuild the columnstore index to empty the delta rowgroups. 此操作与其他移除和合并碎片整理功能相结合,使得在大多数情况下不再需要重新生成索引。This, combined with the other remove and merge defragmentation features makes it no longer necessary to rebuild the index in most situations.

  • ON 将所有行组都强制到列存储中,而不考虑大小和状态(关闭或打开)。ON forces all rowgroups into the columnstore, regardless of size and state (CLOSED or OPEN).

  • OFF 将所有关闭的行组强制到列存储中。OFF forces all CLOSED rowgroups into the columnstore.

SET ( <set_index option> [ ,... n] ) SET ( <set_index option> [ ,... n] )
指定不重新生成或重新组织索引的索引选项。Specifies index options without rebuilding or reorganizing the index. 不能为已禁用的索引指定 SET。SET cannot be specified for a disabled index.

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定索引填充。Specifies index padding. 默认为 OFF。The default is OFF.

ONON
FILLFACTOR 指定的可用空间百分比应用于索引的中间级页。The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index. 如果在 PAD_INDEX 设置为 ON 的同时不指定 FILLFACTOR,则使用 sys.indexes 中存储的填充因子值。If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.

OFF 或未指定 fillfactor OFF or fillfactor is not specified
中间级页已填充到接近容量限制。The intermediate-level pages are filled to near capacity. 这样将至少为索引可以基于中间页中的键集拥有的最大大小的一行留出足够的空间。This leaves sufficient space for at least one row of the maximum size that the index can have, based on the set of keys on the intermediate pages.

有关详细信息,请参阅 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactor FILLFACTOR = fillfactor

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定一个百分比,指示在数据库引擎Database Engine创建或修改索引的过程中,应将每个索引页面的叶级填充到什么程度。Specifies a percentage that indicates how full the 数据库引擎Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor 必须是 1 到 100 之间的整数 。fillfactor must be an integer value from 1 to 100. 默认值为 0。The default is 0. 填充因子的值 0 和 100 在所有方面都是相同的。Fill factor values 0 and 100 are the same in all respects.

显式的 FILLFACTOR 设置只是在索引首次创建或重新生成时应用。An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. 数据库引擎Database Engine并不会在页中动态保持指定的可用空间百分比。The 数据库引擎Database Engine does not dynamically keep the specified percentage of empty space in the pages. 有关详细信息,请参阅 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

若要查看填充因子设置,请使用 sys.indexesTo view the fill factor setting, use sys.indexes.

重要

使用 FILLFACTOR 值创建或更改聚集索引会影响数据占用的存储空间量,因为数据库引擎Database Engine在创建聚集索引时会再分发数据。Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space the data occupies, because the 数据库引擎Database Engine redistributes the data when it creates the clustered index.

SORT_IN_TEMPDB = { ON | OFF } SORT_IN_TEMPDB = { ON | OFF }

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定是否在 tempdb 中存储排序结果。Specifies whether to store the sort results in tempdb. 默认为 OFF。The default is OFF.

ONON
在 tempdb 中存储用于生成索引的中间排序结果 。The intermediate sort results that are used to build the index are stored in tempdb. 如果 tempdb 位于不同于用户数据库的磁盘集中,这样可能会缩短创建索引所需的时间。If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index. 但是,这会增加索引生成期间所使用的磁盘空间量。However, this increases the amount of disk space that is used during the index build.

OFFOFF
中间排序结果与索引存储在同一数据库中。The intermediate sort results are stored in the same database as the index.

如果不需要执行排序操作,或者可以在内存中进行排序,则忽略 SORT_IN_TEMPDB 选项。If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

有关详细信息,请参阅用于索引的 SORT_IN_TEMPDB 选项For more information, see SORT_IN_TEMPDB Option For Indexes.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定在插入操作尝试向唯一索引插入重复键值时的错误响应。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 默认为 OFF。The default is OFF.

ONON
向唯一索引插入重复键值时将出现警告消息。A warning message will occur when duplicate key values are inserted into a unique index. 只有违反唯一性约束的行才会失败。Only the rows violating the uniqueness constraint will fail.

OFFOFF
向唯一索引插入重复键值时将出现错误消息。An error message will occur when duplicate key values are inserted into a unique index. 整个 INSERT 操作将被回滚。The entire INSERT operation will be rolled back.

对于对视图上创建的索引、非唯一索引、XML 索引、空间索引以及筛选的索引,IGNORE_DUP_KEY 不能设置为 ON。IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

若要查看 IGNORE_DUP_KEY,请使用 sys.indexesTo view IGNORE_DUP_KEY, use sys.indexes.

在向后兼容的语法中,WITH IGNORE_DUP_KEY 等效于 WITH IGNORE_DUP_KEY = ON。In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否重新计算分布统计信息。Specifies whether distribution statistics are recomputed. 默认为 OFF。The default is OFF.

ONON
不会自动重新计算过时的统计信息。Out-of-date statistics are not automatically recomputed.

OFFOFF
启用统计信息自动更新功能。Automatic statistics updating are enabled.

若要恢复统计信息自动更新,请将 STATISTICS_NORECOMPUTE 设置为 OFF,或执行 UPDATE STATISTICS 但不包含 NORECOMPUTE 子句。To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

重要

如果禁用分发统计信息的自动重新计算,可能会阻止查询优化器为涉及该表的查询挑选最佳执行计划。Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries that involve the table.

STATISTICS_INCREMENTAL = { ON | OFF } STATISTICS_INCREMENTAL = { ON | OFF }
为 ON 时,根据分区统计信息创建统计信息 。When ON, the statistics created are per partition statistics. 为 OFF 时,删除统计信息树并且 SQL ServerSQL Server 重新计算统计信息 。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 默认为 OFFThe default is OFF.

如果不支持每个分区统计信息,将忽略该选项并生成警告。If per partition statistics are not supported the option is ignored and a warning is generated. 对于以下统计信息类型,不支持增量统计信息:Incremental stats are not supported for following statistics types:

  • 使用未与基表的分区对齐的索引创建的统计信息。Statistics created with indexes that are not partition-aligned with the base table.
  • 对 Always On 可读辅助数据库创建的统计信息。Statistics created on Always On readable secondary databases.
  • 对只读数据库创建的统计信息。Statistics created on read-only databases.
  • 对筛选的索引创建的统计信息。Statistics created on filtered indexes.
  • 对视图创建的统计信息。Statistics created on views.
  • 对内部表创建的统计信息。Statistics created on internal tables.
  • 使用空间索引或 XML 索引创建的统计信息。Statistics created with spatial indexes or XML indexes.

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

ONLINE = { ON | OFF } <同样适用于 rebuild_index_option>ONLINE = { ON | OFF } <as applies to rebuild_index_option>
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 默认为 OFF。The default is OFF.

对于 XML 索引或空间索引,仅支持 ONLINE = OFF。如果 ONLINE 设置为 ON,则会引发错误。For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised.

备注

MicrosoftMicrosoft SQL ServerSQL Server的各版本中均不提供联机索引操作。Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能列表,请参阅 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 的版本和支持的功能SQL Server 2017 的版本和支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016 (13.x)SQL Server 2016 (13.x) and Editions and Supported Features for SQL Server 2017.

ONON
在索引操作期间不持有长期表锁。Long-term table locks are not held for the duration of the index operation. 在索引操作的主要阶段,源表上只使用意向共享 (IS) 锁。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 这样,即可继续对基础表和索引进行查询或更新。This allows queries or updates to the underlying table and indexes to continue. 操作开始时,将对源对象保持极短时间的共享 (S) 锁。At the start of the operation, a Shared (S) lock is very briefly held on the source object. 操作结束时,如果创建非聚集索引,将对源持有极短时间的 S 锁;当联机创建或删除聚集索引时,或者重新生成聚集或非聚集索引时,将获取 SCH-M(架构修改)锁。At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. 对本地临时表创建索引时,ONLINE 不能设置为 ON。ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFFOFF
在索引操作期间应用表锁。Table locks are applied for the duration of the index operation. 创建、重新生成或删除聚集索引、空间索引或 XML 索引或者重新生成或删除非聚集索引的脱机索引操作将获得对表的架构修改 (Sch-M) 锁。An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. 这样可以防止所有用户在操作期间访问基础表。This prevents all user access to the underlying table for the duration of the operation. 创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. 这样可以防止更新基础表,但允许读操作(如 SELECT 语句)。This prevents updates to the underlying table but allows read operations, such as SELECT statements.

有关详细信息,请参阅联机索引操作的工作方式For more information, see How Online Index Operations Work.

索引(包括全局临时表中的索引)可以联机重新生成,但以下索引除外:Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:

  • XML 索引XML indexes

  • 本地临时表中的索引Indexes on local temp tables

  • 分区索引的子集(可以联机重新构建整个分区索引。)A subset of a partitioned index (An entire partitioned index can be rebuilt online.)

  • V12 之前的SQL 数据库SQL Database以及 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 之前的 SQL Server 在基表包含 varchar(max)varbinary(max) 列时,不允许将 ONLINE 选项用于聚集索引生成或重新生成操作。SQL 数据库SQL Database prior to V12, and SQL Server prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x), do not permit the ONLINE option for clustered index build or rebuild operations when the base table contains varchar(max) or varbinary(max) columns.

RESUMABLE = { ON | OFF}RESUMABLE = { ON | OFF}

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

指定联机索引操作是否可恢复。Specifies whether an online index operation is resumable.

ON 索引操作可恢复。ON Index operation is resumable.

OFF 索引操作不可恢复。OFF Index operation is not resumable.

MAX_DURATION = time [MINUTES],与 RESUMABLE = ON 一起使用(要求 ONLINE = ON) 。MAX_DURATION = time [MINUTES] used with RESUMABLE = ON (requires ONLINE = ON).

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

指示可恢复联机索引操作在暂停之前执行的时间(以分钟为单位指定的整数值)。Indicates time (an integer value specified in minutes) that a resumable online index operation is executed before being paused.

ALLOW_ROW_LOCKS = { ON | OFF } ALLOW_ROW_LOCKS = { ON | OFF }

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定是否允许行锁。Specifies whether row locks are allowed. 默认值为 ON。The default is ON.

ONON
在访问索引时允许使用行锁。Row locks are allowed when accessing the index. 数据库引擎Database Engine确定何时使用行锁。The 数据库引擎Database Engine determines when row locks are used.

OFFOFF
不使用行锁。Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF } ALLOW_PAGE_LOCKS = { ON | OFF }

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定是否允许使用页锁。Specifies whether page locks are allowed. 默认值为 ON。The default is ON.

ONON
访问索引时允许使用页锁。Page locks are allowed when you access the index. 数据库引擎Database Engine确定何时使用页锁。The 数据库引擎Database Engine determines when page locks are used.

OFFOFF
不使用页锁。Page locks are not used.

备注

ALLOW_PAGE_LOCKS 设置为 OFF 时,无法重新组织索引。An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

适用于SQL Server 2019(预览版)SQL Server 2019 preview 及更高版本。Applies to: SQL Server 2019(预览版)SQL Server 2019 preview and later.

指定是否针对最后一页插入争用进行优化。Specifies whether or not to optimize for last-page insert contention. 默认为 OFF。The default is OFF. 有关详细信息,请参阅“CREATE INDEX”页的顺序键部分。See the Sequential Keys section of the CREATE INDEX page for more information.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

在索引操作期间替代 max degree of parallelism 配置选项 。Overrides the max degree of parallelism configuration option for the duration of the index operation. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option. 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大数量为 64 个处理器。The maximum is 64 processors.

重要

虽然所有 XML 索引在语法上都支持 MAXDOP 选项,但对于空间索引或主 XML 索引,ALTER INDEX 当前只使用一个处理器。Although the MAXDOP option is syntactically supported for all XML indexes, for a spatial index or a primary XML index, ALTER INDEX currently uses only a single processor.

max_degree_of_parallelism 可以是 :max_degree_of_parallelism can be:

11
取消生成并行计划。Suppresses parallel plan generation.

>1>1
将并行索引操作中使用的最大处理器数量限制为指定数量。Restricts the maximum number of processors used in a parallel index operation to the specified number.

0(默认值)0 (default)
根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。Uses the actual number of processors or fewer based on the current system workload.

有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

备注

并非在 MicrosoftMicrosoft SQL ServerSQL Server 的每个版本中均支持并行索引操作。Parallel index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能列表,请参阅 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 的版本和支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016 (13.x)SQL Server 2016 (13.x).

COMPRESSION_DELAY = { 0 |duration [Minutes] } COMPRESSION_DELAY = { 0 |duration [Minutes] }
SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本支持此功能This feature is available Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)

对于基于磁盘的表,延迟指定处于关闭状态的增量行组在 SQL Server 可以将它压缩为压缩行组之前,必须保持为增量行组的最小分钟数。For a disk-based table, delay specifies the minimum number of minutes a delta rowgroup in the CLOSED state must remain in the delta rowgroup before SQL Server can compress it into the compressed rowgroup. 由于基于磁盘的表不对单个行跟踪插入和更新时间,因此 SQL Server 会将该延迟应用于处于关闭状态的增量行组。Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.
默认为 0 分钟。The default is 0 minutes.

默认为 0 分钟。The default is 0 minutes.

有关何时使用 COMPRESSION_DELAY 的建议,请参阅实时运营分析的列存储索引。For recommendations on when to use COMPRESSION_DELAY, see Columnstore Indexes for Real-Time Operational Analytics.

DATA_COMPRESSIONDATA_COMPRESSION
为指定的索引、分区号或分区范围指定数据压缩选项。Specifies the data compression option for the specified index, partition number, or range of partitions. 选项如下所示:The options are as follows:

NONE
不压缩索引或指定的分区。Index or specified partitions are not compressed. 这不适用于列存储索引。This does not apply to columnstore indexes.

ROWROW
使用行压缩来压缩索引或指定的分区。Index or specified partitions are compressed by using row compression. 这不适用于列存储索引。This does not apply to columnstore indexes.

PAGEPAGE
使用页压缩来压缩索引或指定的分区。Index or specified partitions are compressed by using page compression. 这不适用于列存储索引。This does not apply to columnstore indexes.

COLUMNSTORECOLUMNSTORE

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE 指定对使用 COLUMNSTORE_ARCHIVE 选项压缩的索引或指定分区进行解压缩。COLUMNSTORE specifies to decompress the index or specified partitions that are compressed with the COLUMNSTORE_ARCHIVE option. 在还原数据时,将继续通过用于所有列存储索引的列存储压缩对数据进行压缩。When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore indexes.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE_ARCHIVE 会进一步将指定分区压缩为更小的大小。COLUMNSTORE_ARCHIVE will further compress the specified partition to a smaller size. 这可用于存档,或者用于要求更小存储大小并且可以付出更多时间来进行存储和检索的其他情形。This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

有关压缩的详细信息,请参阅数据压缩For more information about compression, see Data Compression.

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

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

指定对其应用 DATA_COMPRESSION 设置的分区。Specifies the partitions to which the DATA_COMPRESSION setting applies. 如果索引未分区,则 ON PARTITIONS 参数将产生错误。If the index is not partitioned, the ON PARTITIONS argument will generate an error. 如果不提供 ON PARTITIONS 子句,则 DATA_COMPRESSION 选项将应用于分区索引的所有分区。If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.

可以按以下方式指定 <partition_number_expression>:<partition_number_expression> can be specified in the following ways:

  • 提供分区号,例如:ON PARTITIONS (2)。Provide the number for a partition, for example: ON PARTITIONS (2).

  • 为多个单独分区提供分区号,用逗号分隔,例如:ON PARTITIONS (1, 5)。Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).

  • 提供范围和单个分区:ON PARTITIONS (2, 4, 6 TO 8)。Provide both ranges and individual partitions: ON PARTITIONS (2, 4, 6 TO 8).

<range> 可指定为由单词 TO 隔开的分区号,例如:ON PARTITIONS (6 TO 8)。<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

若要为不同分区设置不同的数据压缩类型,请多次指定 DATA_COMPRESSION 选项,例如:To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

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>ONLINE = { ON | OFF } <as applies to single_partition_rebuild_index_option>
指定索引或基础表的索引分区是否可以联机或脱机重新生成。Specifies whether an index or an index partition of an underlying table can be rebuilt online or offline. 如果 REBUILD 联机执行 (ON),则索引操作期间可以用此表中的数据进行查询和修改数据。If REBUILD is performed online (ON) the data in this table is available for queries and data modification during the index operation. 默认为 OFFThe default is OFF.

ONON
在索引操作期间不持有长期表锁。Long-term table locks are not held for the duration of the index operation. 在索引操作的主要阶段,源表上只使用意向共享 (IS) 锁。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 索引重新生成开始时表上需要一个 S 锁,联机重新生成索引结束时表上需要一个 Sch-M 锁。An S-lock on the table is required in the Starting of the index rebuild and a Sch-M lock on the table at the end of the online index rebuild. 不过两个锁都是短的元数据锁,特别是 Sch-M 锁必须等待所有阻塞事务完成。Although both locks are short metadata locks, especially the Sch-M lock must wait for all blocking transactions to be completed. 在等待期间,Sch-M 锁在访问同一表时阻止在此锁后等待的所有其他事务。During the wait time the Sch-M lock blocks all other transactions that wait behind this lock when accessing the same table.

备注

联机索引重新生成可以设置本节稍后介绍的 low_priority_lock_wait 选项 。Online index rebuild can set the low_priority_lock_wait options described later in this section.

OFFOFF
在索引操作期间应用表锁。Table locks are applied for the duration of the index operation. 这样可以防止所有用户在操作期间访问基础表。This prevents all user access to the underlying table for the duration of the operation.

WAIT_AT_LOW_PRIORITY,仅与 ONLINE=ON 一起使用。WAIT_AT_LOW_PRIORITY used with ONLINE=ON only.

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

联机索引重新生成必须等待对此表执行的阻塞操作。An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY 表示联机索引重新生成操作将等待低优先级锁,从而允许其他操作在该联机索引生成操作正在等待时继续进行。WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. 省略 WAIT AT LOW PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). 有关详细信息,请参阅 WAIT_AT_LOW_PRIORITYFor more information, see WAIT_AT_LOW_PRIORITY.

MAX_DURATION = time [MINUTES] MAX_DURATION = time [MINUTES]

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

联机索引重新生成锁将在执行 DDL 命令时以低优先级等待的等待时间(以分钟为单位指定的整数值)。The wait time (an integer value specified in minutes) that the online index rebuild locks will wait with low priority when executing the DDL command. 如果操作被阻塞 MAX_DURATION 时间,则将执行某一 ABORT_AFTER_WAIT 操作 。If the operation is blocked for the MAX_DURATION time, one of the ABORT_AFTER_WAIT actions will be executed. MAX_DURATION 时间始终以分钟为单位,MINUTES 一词可以省略 。MAX_DURATION time is always in minutes, and the word MINUTES can be omitted.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ] ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

NONE
继续以普通(常规)优先级等待锁。Continue waiting for the lock with normal (regular) priority.

SELFSELF
不采取任何操作,直接退出当前执行的联机索引重新生成 DDL 操作。Exit the online index rebuild DDL operation currently being executed without taking any action.

BLOCKERSBLOCKERS
终止阻塞联机索引重新生成 DDL 操作的所有用户事务以使操作可以继续。Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue. BLOCKERS 选项要求登录名拥有 ALTER ANY CONNECTION 权限。The BLOCKERS option requires the login to have ALTER ANY CONNECTION permission.

RESUMERESUME

适用对象:自 SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)

恢复已手动或由于失败而暂停的索引操作。Resume an index operation that is paused manually or due to a failure.

MAX_DURATION,与 RESUMABLE=ON 一起使用MAX_DURATION used with RESUMABLE=ON

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

可恢复联机索引操作在恢复之后执行的时间(以分钟为单位指定的整数值)。The time (an integer value specified in minutes) the resumable online index operation is executed after being resumed. 该时间过后,如果可恢复操作仍在运行,则它会暂停。Once the time expires, the resumable operation is paused if it is still running.

WAIT_AT_LOW_PRIORITY,与 RESUMABLE=ONONLINE = ON 一起使用。WAIT_AT_LOW_PRIORITY used with RESUMABLE=ON and ONLINE = ON.

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

在暂停之后恢复联机索引必须等待对此表执行的阻塞操作。Resuming an online index rebuild after a pause has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY 表示联机索引重新生成操作将等待低优先级锁,从而允许其他操作在该联机索引生成操作正在等待时继续进行。WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. 省略 WAIT AT LOW PRIORITY 选项等效于 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). 有关详细信息,请参阅 WAIT_AT_LOW_PRIORITYFor more information, see WAIT_AT_LOW_PRIORITY.

PAUSEPAUSE

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

暂停可恢复联机索引重新生成操作。Pause a resumable online index rebuild operation.

ABORTABORT

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

中止已声明为可恢复的正在运行或已暂停的索引操作。Abort a running or paused index operation that was declared as resumable. 必须显式执行 ABORT 命令才能终止可恢复索引重新生成操作。You have to explicitly execute an ABORT command to terminate a resumable index rebuild operation. 失败或暂停可恢复索引操作不会终止其执行;而是将操作停留在无限期暂停状态。Failure or pausing a resumable index operation does not terminate its execution; rather, it leaves the operation in an indefinite pause state.

RemarksRemarks

ALTER INDEX 不能用于对索引重新分区或将索引移到其他文件组。ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. 此语句不能用于修改索引定义,如添加或删除列,或更改列的顺序。This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. 使用带有 DROP_EXISTING 子句的 CREATE INDEX 执行这些操作。Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

未显式指定选项时,则应用当前设置。When an option is not explicitly specified, the current setting is applied. 例如,如果未在 REBUILD 子句中指定 FILLFACTOR 设置,将在重新生成过程中使用系统目录中存储的填充因子值。For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. 若要查看当前索引选项设置,请使用 sys.indexesTo view the current index option settings, use sys.indexes.

系统目录中不存储 ONLINE、MAXDOP 和 SORT_IN_TEMPDB 的值。The values for ONLINE, MAXDOP, and SORT_IN_TEMPDB are not stored in the system catalog. 除非在索引语句中指定,否则,将使用选项的默认值。Unless specified in the index statement, the default value for the option is used.

在多处理器计算机中,就像其他查询那样,ALTER INDEX ...REBUILD 自动使用更多处理器来执行与修改索引相关联的扫描和排序操作。On multiprocessor computers, just like other queries do, ALTER INDEX ... REBUILD automatically uses more processors to perform the scan and sort operations that are associated with modifying the index. 运行 ALTER INDEX ...REORGANIZE 时,无论是否有 LOB_COMPACTION,max degree of parallelism 值均为单个线程化操作。When you run ALTER INDEX ... REORGANIZE, with or without LOB_COMPACTION, the max degree of parallelism value is a single threaded operation. 有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

重要

如果索引所在的文件组脱机或设置为只读,则无法重新组织或重新生成索引。An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. 如果指定了关键字 ALL,但有一个或多个索引位于脱机文件组或只读文件组中,该语句将失败。When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

重新生成索引Rebuilding Indexes

重新生成索引将会删除并重新创建索引。Rebuilding an index drops and re-creates the index. 这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间,然后对连续页中的索引行重新排序。This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. 如果指定 ALL,将删除表中的所有索引,然后在单个事务中重新生成。When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. 不必预先删除外键约束。Foreign key constraints do not have to be dropped in advance. 重新生成具有 128 个区或更多区的索引时,数据库引擎Database Engine延迟实际的页释放及其关联的锁,直到事务提交。When indexes with 128 extents or more are rebuilt, the 数据库引擎Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

有关详细信息,请参阅 重新组织和重新生成索引For more information, see Reorganize and Rebuild Indexes.

备注

重新生成或重新组织小索引不会减少碎片。Rebuilding or reorganizing small indexes often does not reduce fragmentation. 小索引的页面有关存储在混合盘区中。The pages of small indexes are sometimes stored on mixed extents. 混合区最多可由八个对象共享,因此在重新组织或重新生成小索引之后可能不会减少小索引中的碎片。Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

重要

SQL ServerSQL Server 中创建或重新生成索引时,将通过扫描表中的所有行来创建或更新统计信息。When an index is created or rebuilt in SQL ServerSQL Server, statistics are created or updated by scanning all the rows in the table.

但是,从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,当创建或重新生成已分区索引时,不会通过扫描表中的所有行来创建统计信息。However, starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. 相反,查询优化器使用默认采样算法来生成这些统计信息。Instead, the query optimizer uses the default sampling algorithm to generate these statistics. 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息,请使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

在早期版本的 SQL ServerSQL Server 中,您有时可以重新生成非聚集索引以纠正硬件故障导致的不一致问题。In earlier versions of SQL ServerSQL Server, you could sometimes rebuild a nonclustered index to correct inconsistencies caused by hardware failures.
SQL Server 2008SQL Server 2008 和更高版本中,您仍然可以通过脱机重新生成非聚集索引来纠正索引和聚集索引之间的这种不一致问题。In SQL Server 2008SQL Server 2008 and later, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. 但是,您不能通过联机重新生成索引来纠正非聚集索引的不一致,因为联机重新生成机制将会使用现有的非聚集索引作为重新生成的基础,因此仍存在不一致。However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism will use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. 脱机重新生成索引有时会强制扫描聚集索引(或堆)并因此删除不一致。Rebuilding the index offline can sometimes force a scan of the clustered index (or heap) and so remove the inconsistency. 要确保从聚集索引重新生成,请删除并重新创建非聚集索引。To assure a rebuild from the clustered index, drop and recreate the non-clustered index. 与早期版本一样,建议通过从备份还原受影响的数据来从不一致状态进行恢复;但是,您可以通过脱机重新生成非聚集索引来纠正索引的不一致。As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. 有关详细信息,请参阅 DBCC CHECKDB (Transact-SQL)For more information, see DBCC CHECKDB (Transact-SQL).

若要重新生成聚集列存储索引,SQL ServerSQL Server 将:To rebuild a clustered columnstore index, SQL ServerSQL Server:

  1. 在重新生成进行时获取表或分区上的排他锁。Acquires an exclusive lock on the table or partition while the rebuild occurs. 在重新生成期间数据“处于脱机状态”并且不可用。The data is "offline" and unavailable during the rebuild.

  2. 通过物理删除已从表中逻辑上删除的行对列存储进行碎片整理;已删除的字节在物理介质上回收。Defragments the columnstore by physically deleting rows that have been logically deleted from the table; the deleted bytes are reclaimed on the physical media.

  3. 从原始列存储索引(包括增量存储)中读取所有数据。Reads all data from the original columnstore index, including the deltastore. 它将数据合并到新的行组中,并且将行组压缩到列存储中。It combines the data into new rowgroups, and compresses the rowgroups into the columnstore.

  4. 要求物理介质上的空间,以便在进行重新生成时存储列存储索引的两个副本。Requires space on the physical media to store two copies of the columnstore index while the rebuild is taking place. 在重新生成完成后,SQL ServerSQL Server 将删除原始聚集列存储索引。When the rebuild is finished, SQL ServerSQL Server deletes the original clustered columnstore index.

  5. 对于具有有序聚合列存储索引的 Azure SQL 数据仓库表,ALTER INDEX REBUILD 将对数据重新排序。For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data. 重新生成操作期间监视 tempdb。Monitor tempdb during rebuild operations. 如果需要更多的 tempdb 空间,可扩展数据仓库。If you need more tempdb space, you can scale up the data warehouse. 完成索引重新生成之后,缩小为原空间大小。Scale back down once the index rebuild is complete.

重新组织索引Reorganizing Indexes

使用最少系统资源重新组织索引。Reorganizing an index uses minimal system resources. 通过对叶级页以物理方式重新排序,使之与叶节点的从左到右的逻辑顺序相匹配,进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. 重新组织还会压缩索引页。Reorganizing also compacts the index pages. 压缩基于现有的填充因子值。Compaction is based on the existing fill factor value. 若要查看填充因子设置,请使用 sys.indexesTo view the fill factor setting, use sys.indexes.

如果指定 ALL,将重新组织表中的关系索引(包括聚集索引和非聚集索引)和 XML 索引。When ALL is specified, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized. 指定 ALL 时应用某些限制,请参阅本文“参数”部分的 ALL 定义。Some restrictions apply when specifying ALL, refer to the definition for ALL in the Arguments section of this article.

有关详细信息,请参阅 重新组织和重新生成索引For more information, see Reorganize and Rebuild Indexes.

重要

SQL ServerSQL Server 中重新组织索引时,不更新统计信息。When an index is reorganized in SQL ServerSQL Server, statistics are not updated.

重要

对于具有有序聚合列存储索引的 Azure SQL 数据仓库表,ALTER INDEX REORGANIZE 不会对数据重新排序。For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. 要对数据重新排序,可使用 ALTER INDEX REBUILDTo resort the data use ALTER INDEX REBUILD.

禁用索引Disabling Indexes

禁用索引可防止用户访问该索引,对于聚集索引,还可防止用户访问基础表数据。Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. 索引定义保留在系统目录中。The index definition remains in the system catalog. 对视图禁用非聚集索引或聚集索引会以物理方式删除索引数据。Disabling a nonclustered index or clustered index on a view physically deletes the index data. 禁用聚集索引将阻止对数据的访问,但在删除或重新生成索引之前,数据在 B 树中一直保持未维护的状态。Disabling a clustered index prevents access to the data, but the data remains unmaintained in the B-tree until the index is dropped or rebuilt. 若要查看已启用索引或已禁用的索引的状态,请查询 sys.indexes 目录视图中的 is_disabled 列。To view the status of an enabled or disabled index, query the is_disabled column in the sys.indexes catalog view.

如果表位于事务复制发布中,则无法禁用任何与主键列关联的索引。If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. 复制需要使用这些索引。These indexes are required by replication. 若要禁用索引,必须先从发布中删除该表。To disable an index, you must first drop the table from the publication. 有关详细信息,请参阅发布数据和数据库对象For more information, see Publish Data and Database Objects.

使用 ALTER INDEX REBUILD 语句或 CREATE INDEX WITH DROP_EXISTING 语句启用索引。Use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement to enable the index. 重新生成已禁用聚集索引不能在 ONLINE 选项设置为 ON 时执行。Rebuilding a disabled clustered index cannot be performed with the ONLINE option set to ON. 有关详细信息,请参阅 禁用索引和约束For more information, see Disable Indexes and Constraints.

“设置选项”Setting Options

可以为指定索引设置选项 ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKS、OPTIMIZE_FOR_SEQUENTIAL_KEY、IGNORE_DUP_KEY 和 STATISTICS_NORECOMPUTE,而无需重新生成或重新组织此索引。You can set the options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY and STATISTICS_NORECOMPUTE for a specified index without rebuilding or reorganizing that index. 修改的值立即应用于索引。The modified values are immediately applied to the index. 若要查看这些设置,请使用 sys.indexesTo view these settings, use sys.indexes. 有关详细信息,请参阅 设置索引选项For more information, see Set Index Options.

行锁和页锁选项Row and Page Locks Options

如果 ALLOW_ROW_LOCKS = ON 并且 ALLOW_PAGE_LOCK = ON,则当访问索引时将允许行级别、页级别和表级别的锁。When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-level, page-level, and table-level locks are allowed when you access the index. 数据库引擎Database Engine将选择相应的锁,并且可以将锁从行锁或页锁升级到表锁。The 数据库引擎Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

如果 ALLOW_ROW_LOCKS = OFF 并且 ALLOW_PAGE_LOCK = OFF,则当访问索引时只允许表级锁。When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when you access the index.

设置行锁或页锁选项时,如果指定 ALL,这些设置将应用于所有索引。If ALL is specified when the row or page lock options are set, the settings are applied to all indexes. 基础表为堆时,通过以下方式应用这些设置:When the underlying table is a heap, the settings are applied in the following ways:

ALLOW_ROW_LOCKS = ON 或 OFFALLOW_ROW_LOCKS = ON or OFF 应用于堆和任何关联的非聚集索引。To the heap and any associated nonclustered indexes.
ALLOW_PAGE_LOCKS = ONALLOW_PAGE_LOCKS = ON 应用于堆和任何关联的非聚集索引。To the heap and any associated nonclustered indexes.
ALLOW_PAGE_LOCKS = OFFALLOW_PAGE_LOCKS = OFF 完全针对非聚集索引。Fully to the nonclustered indexes. 这意味着不允许对非聚集索引使用所有页锁。This means that all page locks are not allowed on the nonclustered indexes. 在堆中,仅不允许使用有页的共享 (S) 锁、更新 (U) 锁和排他 (X) 锁。On the heap, only the shared (S), update (U) and exclusive (X) locks for the page are not allowed. 数据库引擎Database Engine仍然可以获取意向页锁(IS、IU 或 IX),供内部使用。The 数据库引擎Database Engine can still acquire an intent page lock (IS, IU or IX) for internal purposes.

联机索引操作Online Index Operations

重新生成索引且 ONLINE 选项设置为 ON 时,基础对象、表和关联的索引均可用于查询和数据修改。When rebuilding an index and the ONLINE option is set to ON, the underlying objects, the tables and associated indexes, are available for queries and data modification. 您也可以联机重新生成单个分区上某索引的一部分。You can also rebuild online a portion of an index residing on a single partition. 更改过程中,排他表锁只保留非常短的时间。Exclusive table locks are held only for a very short amount of time during the alteration process.

重新组织索引始终联机执行。Reorganizing an index is always performed online. 该进程不长期保留锁,因此,不阻塞正在运行的查询或更新。The process does not hold locks long term and, therefore, does not block queries or updates that are running.

只有在执行以下操作时,才能对同一个表或表部分执行并发联机索引操作:You can perform concurrent online index operations on the same table or table partition only when doing the following:

  • 创建多个非聚集索引。Creating multiple nonclustered indexes.
  • 在同一个表中重新组织不同索引。Reorganizing different indexes on the same table.
  • 在同一个表中重新生成不重叠的索引时,重新组织不同的索引。Reorganizing different indexes while rebuilding nonoverlapping indexes on the same table.

同一时间执行的所有其他联机索引操作都将失败。All other online index operations performed at the same time fail. 例如,您不能在同一个表中同时重新生成两个索引或更多索引,也不能在同一个表中重新生成现有索引时创建新的索引。For example, you cannot rebuild two or more indexes on the same table concurrently, or create a new index while rebuilding an existing index on the same table.

可恢复索引操作Resumable index operations

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

Online index rebuild 可使用 RESUMABLE = ON 选项指定为可恢复。Online index rebuild is specified as resumable using the RESUMABLE = ON option.

  • RESUMABLE 选项对于给定索引在元数据不持久,并且仅适用于当前 DDL 语句的持续时间。The RESUMABLE option is not persisted in the metadata for a given index and applies only to the duration of a current DDL statement. 因此,必须显式指定 RESUMABLE = ON 子句才能启用可恢复性。Therefore, the RESUMABLE = ON clause must be specified explicitly to enable resumability.

  • RESUMABLE = ON 选项或 low_priority_lock_wait 参数选项支持 MAX_DURATION 选项 。MAX_DURATION option is supported for RESUMABLE = ON option or the low_priority_lock_wait argument option.

    • 用于 RESUMABLE 选项的 MAX_DURATION 为重新生成的索引指定时间间隔。MAX_DURATION for RESUMABLE option specifies the time interval for an index being rebuild. 使用此时间之后,索引重新生成会暂停或完成其执行。Once this time is used the index rebuild is either paused or it completes its execution. 由用户确定何时可以恢复暂停的索引的重新生成。User decides when a rebuild for a paused index can be resumed. MAX_DURATION 时间 (以分钟为单位)必须大于 0 分钟,且小于等于一周(7 * 24 * 60 = 10080 分钟)。The time in minutes for MAX_DURATION must be greater than 0 minutes and less or equal one week (7 * 24 * 60 = 10080 minutes). 让索引操作长时间暂停可能会影响特定表的 DML 性能以及数据库磁盘容量,因为原始索引和新创建的索引需要磁盘空间并且需要在 DML 操作期间更新。Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations. 如果省略 MAX_DURATION 选项,则索引操作会继续,直到其完成或发生失败。If MAX_DURATION option is omitted, the index operation will continue until its completion or until a failure occurs.
    • 通过 <low_priority_lock_wait> 参数选项可以确定在 SCH-M 锁上阻塞时,索引操作如何才能继续。The <low_priority_lock_wait> argument option allows you to decide how the index operation can proceed when blocked on the SCH-M lock.
  • 使用相同参数重新执行原始 ALTER INDEX REBUILD 语句会恢复暂停的索引重新生成操作。Re-executing the original ALTER INDEX REBUILD statement with the same parameters resumes a paused index rebuild operation. 还可以通过执行 ALTER INDEX RESUME 语句来恢复暂停的索引重新生成操作。You can also resume a paused index rebuild operation by executing the ALTER INDEX RESUME statement.

  • 可恢复索引不支持 SORT_IN_TEMPDB=ON 选项The SORT_IN_TEMPDB=ON option is not supported for resumable index

  • 无法在显式事务(不能属于 tran ... commit 块)中执行具有“RESUMEABLE = ON”的 DDL 命令。The DDL command with RESUMABLE=ON cannot be executed inside an explicit transaction (cannot be part of begin tran ... commit block).

  • 只有暂停的索引操作才可恢复。Only index operations that are paused are resumable.

  • 恢复暂停的索引操作时,可以将 MAXDOP 值更改为新值。When resuming an index operation that is paused, you can change the MAXDOP value to a new value. 如果在恢复暂停的索引操作时未指定 MAXDOP,则采用最后一个 MAXDOP 值。If MAXDOP is not specified when resuming an index operation that is paused, the last MAXDOP value is taken. 如果对于索引重新生成操作完全未指定 MAXDOP 选项,则采用默认值。IF the MAXDOP option is not specified at all for index rebuild operation, the default value is taken.

  • 若要立即暂停索引操作,则可以停止正在进行的命令 (CTRL-C),也可以执行 ALTER INDEX PAUSE 命令或 KILL session_id 命令。To pause immediately the index operation, you can stop the ongoing command (Ctrl-C) or you can execute the ALTER INDEX PAUSE command or the KILL session_id command. 暂停命令之后,可以使用 RESUME 选项恢复它。Once the command is paused it can be resumed using RESUME option.

  • ABORT 命令可终止承载原始索引重新生成的会话,并中止索引操作The ABORT command kills the session that hosted the original index rebuild and aborts the index operation

  • 除了以下情况,可恢复索引重新生成无需额外资源No extra resources are required for resumable index rebuild except for

    • 使索引保持生成所需的附加空间,包括索引暂停的时间Additional space required to keep the index being built, including the time when index is being paused
    • 阻止任何 DDL 修改的 DDL 状态A DDL state preventing any DDL modification
  • 虚影清除会在索引暂停阶段期间运行,但是它会在索引运行期间暂停The ghost cleanup will be running during the index pause phase, but it will be paused during index run
    对于可恢复索引重新生成操作会禁用以下功能The following functionality is disabled for resumable index rebuild operations

    • RESUMABLE=ON 不支持重新生成已禁用的索引Rebuilding an index that is disabled is not supported with RESUMABLE=ON
    • ALTER INDEX REBUILD ALL 命令ALTER INDEX REBUILD ALL command
    • 使用索引重新生成的 ALTER TABLEALTER TABLE using index rebuild
    • 无法在显式事务(不能属于 tran ... commit 块)中执行具有“RESUMEABLE = ON”的 DDL 命令DDL command with "RESUMEABLE = ON" cannot be executed inside an explicit transaction (cannot be part of begin tran ... commit block)
    • 重新生成具有已计算或 TIMESTAMP 列(作为键列)的索引。Rebuild an index that has computed or TIMESTAMP column(s) as key columns.
  • 如果基表包含 LOB 列,则可恢复聚集索引重新生成在此操作开始时需要 Sch-M 锁In case the base table contains LOB column(s) resumable clustered index rebuild requires a Sch-M lock in the Starting of this operation

备注

DDL 命令会运行到完成、暂停或失败。The DDL command runs until it completes, pauses or fails. 如果命令暂停,则会发出错误,指示操作已暂停并且索引创建未完成。In case the command pauses, an error will be issued indicating that the operation was paused and that the index creation did not complete. 可以从 sys.index_resumable_operations 获取有关当前索引状态的详细信息。More information about the current index status can be obtained from sys.index_resumable_operations. 如同之前一样,发生失败时,也会发出错误。As before in case of a failure an error will be issued as well.

有关详细信息,请参阅 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

具有联机索引操作的 WAIT_AT_LOW_PRIORITYWAIT_AT_LOW_PRIORITY with online index operations

要执行联机索引重新生成的 DDL 语句,必须完成对某一特定表运行的所有活动阻塞事务。In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. 在联机索引重新生成执行时,它会阻塞准备对此表执行的所有新事务。When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. 尽管联机索引重新生成锁的持续时间非常短,但等待某一给定表的所有打开的事务完成并阻塞新事务启动可能对吞吐量造成很大影响,导致工作负荷变慢或超时,并严重限制对基础表的访问。Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. WAIT_AT_LOW_PRIORITY 选项允许 DBA 管理联机索引重新生成需要的 S 锁和 Sch-M 锁,并允许他们选择 3 个选项之一。The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. 在所有 3 种情况下,如果等待期间 ( (MAX_DURATION = n [minutes]) ) 没有阻塞活动,则联机索引重新生成会立即执行,而不等待 DDL 语句完成。In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ), there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.

空间索引限制Spatial Index Restrictions

重新生成空间索引时,基础用户表在索引操作持续期间不可用,因为空间索引持有架构锁。When you rebuild a spatial index, the underlying user table is unavailable for the duration of the index operation because the spatial index holds a schema lock.

对用户表的某一列定义了空间索引时,无法修改该表中的 PRIMARY KEY 约束。The PRIMARY KEY constraint in the user table cannot be modified while a spatial index is defined on a column of that table. 若要更改 PRIMARY KEY 约束,首先要删除该表的每个空间索引。To change the PRIMARY KEY constraint, first drop every spatial index of the table. 修改 PRIMARY KEY 约束后,您可以重新创建每个空间索引。After modifying the PRIMARY KEy constraint, you can re-create each of the spatial indexes.

在单个分区重新生成操作中,无法指定任何空间索引。In a single partition rebuild operation, you cannot specify any spatial indexes. 但是,您可以在完整的分区重新生成过程中指定空间索引。However, you can specify spatial indexes in a complete partition rebuild.

若要更改特定于某个空间索引的选项(例如 BOUNDING_BOX 或 GRID),您可以使用 CREATE SPATIAL INDEX 语句指定 DROP_EXISTING = ON,或删除该空间索引并创建一个新的空间索引。To change options that are specific to a spatial index, such as BOUNDING_BOX or GRID, you can either use a CREATE SPATIAL INDEX statement that specifies DROP_EXISTING = ON, or drop the spatial index and create a new one. 有关示例,请参阅 CREATE SPATIAL INDEX (Transact-SQL)中的“备注”部分。For an example, see CREATE SPATIAL INDEX (Transact-SQL).

Data CompressionData Compression

有关数据压缩的详细信息,请参阅 数据压缩For a more information about data compression, see Data Compression.

若要评估更改 PAGE 和 ROW 压缩将对表、索引或分区有何影响,请使用 sp_estimate_data_compression_savings 存储过程。To evaluate how changing PAGE and ROW compression will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

以下限制适用于已分区索引:The following restrictions apply to partitioned indexes:

  • 使用 ALTER INDEX ALL ... 时,如果相应表具有非对齐索引,则无法更改单个分区的压缩设置。When you use ALTER INDEX ALL ..., you cannot change the compression setting of a single partition if the table has nonaligned indexes.
  • ALTER INDEX <index> ...REBUILD PARTITION ... 语法可重新生成索引的指定分区。The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.
  • ALTER INDEX <index> ...REBUILD WITH ... 语法可重新生成索引的所有分区。The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

统计信息Statistics

在对某个表执行 ALTER INDEX ALL ... 时,只更新与索引相关联的统计信息 。When you execute ALTER INDEX ALL ... on a table, only the statistics associates with indexes are updated. 针对表(而不是索引)自动或手动创建的统计信息不会更新。Automatic or manual statistics created on the table (instead of an index) are not updated.

权限Permissions

若要执行 ALTER INDEX,至少需要对表或视图具有 ALTER 权限。To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

版本说明Version Notes

  • SQL 数据库SQL Database不使用文件组和文件流选项。does not use filegroup and filestream options.
  • 列存储索引在 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 之前不可用。Columnstore indexes are not available prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x).
  • SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本和 SQL 数据库SQL Database提供可恢复索引操作Resumable index operations are available Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and SQL 数据库SQL Database

基本语法示例:Basic syntax example:

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

示例:列存储索引Examples: Columnstore Indexes

这些示例适用于列存储索引。These examples apply to columnstore indexes.

A.A. REORGANIZE 演示REORGANIZE demo

此示例演示 ALTER INDEX REORGANIZE 命令的工作原理。This example demonstrates how the ALTER INDEX REORGANIZE command works. 它创建一个具有多个行组的表,然后演示 REORGANIZE 如何合并行组。It creates a table that has multiple rowgroups, and then demonstrates how REORGANIZE merges the rowgroups.

-- 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 选项并行插入行。Use the TABLOCK option to insert rows in parallel. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,INSERT INTO 操作可以在使用 TABLOCK 时并行运行。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the INSERT INTO operation can run in parallel when TABLOCK is used.

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

运行此命令可查看打开的增量行组。Run this command to see the OPEN delta rowgroups. 行组数取决于并行度。The number of rowgroups depends on the degree of parallelism.

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

运行此命令以将所有关闭和打开的行组强制到列存储中。Run this command to force all CLOSED and OPEN rowgroups into the columnstore.

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

再次运行此命令,你会看到较小行组合并为一个压缩行组。Run this command again and you will see that smaller rowgroups are merged into one compressed rowgroup.

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

B.B. 将关闭的增量行组压缩到列存储中Compress CLOSED delta rowgroups into the columnstore

此示例使用 REORGANIZE 选项将每个关闭的增量行组作为压缩行组压缩到列存储中。This example uses the REORGANIZE option to compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. 这不是必需的,但是在 tuple-mover 压缩关闭的行组的速度不够快时非常有用。This is not necessary, but is useful when the tuple-mover is not compressing CLOSED rowgroups fast enough.

-- Uses AdventureWorksDW  
-- REORGANIZE all partitions  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;  
  
-- REORGANIZE a specific partition  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;  

C.C. 将所有打开和关闭的增量行组压缩到列存储中Compress all OPEN AND CLOSED delta rowgroups into the columnstore

适用范围: SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and SQL 数据库SQL Database

命令 REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) 将每个打开和关闭的增量行组作为压缩行组压缩到列存储中。The command REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) compresses each OPEN and CLOSED delta rowgroup into the columnstore as a compressed rowgroup. 这会清空增量存储,并强制所有行压缩到列存储中。This empties the deltastore and forces all rows to get compressed into the columnstore. 这在执行许多插入操作之后特别有用,因为这些操作将行存储在一个或多个增量行组中。This is useful especially after performing many insert operations since these operations store the rows in one or more delta rowgroups.

REORGANIZE 会合并行组以填充最大行数 <= 1,024,576 的行组。REORGANIZE combines rowgroups to fill rowgroups up to a maximum number of rows <= 1,024,576. 因此,在压缩所有打开和关闭的行组时,最后不会得到与大量其中只包含少量行的压缩行组。Therefore, when you compress all OPEN and CLOSED rowgroups you won't end up with lots of compressed rowgroups that only have a few rows in them. 需要行组尽可能满,以减少压缩大小并提高查询性能。You want rowgroups to be as full as possible to reduce the compressed size and improve query performance.

-- Uses AdventureWorksDW2016  
-- Move all OPEN and CLOSED delta rowgroups into the columnstore.  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  
  
-- For a specific partition, move all OPEN AND CLOSED delta rowgroups into the columnstore  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);  

D.D. 对列存储索引进行联机碎片整理Defragment a columnstore index online

不适用于:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Does not apply to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,REORGANIZE 不仅仅会将增量行组压缩到列存储中。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), REORGANIZE does more than compress delta rowgroups into the columnstore. 它还执行联机碎片整理。It also performs online defragmentation. 首先,它会在删除了行组中 10% 或更多行时物理移除已删除的行,从而减少列存储的大小。First, it reduces the size of the columnstore by physically removing deleted rows when 10% or more of the rows in a rowgroup have been deleted. 然后,它将行组合并在一起以形成更大行组,每个行组最多包含 1,024,576 行。Then, it combines rowgroups together to form larger rowgroups that have up to the maximum of 1,024,576 rows per rowgroups. 更改的所有行组都会重新压缩。All rowgroups that are changed get re-compressed.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,在多数情况下不再需要重新生成列存储索引,因为 REORGANIZE 会物理移除已删除的行并合并行组。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding a columnstore index is no longer necessary in most situations since REORGANIZE physically removes deleted rows and merges rowgroups. COMPRESS_ALL_ROW_GROUPS 选项将所有打开或关闭的增量行组强制到列存储中,这以前只能通过重新生成来进行。The COMPRESS_ALL_ROW_GROUPS option forces all OPEN or CLOSED delta rowgroups into the columnstore which previously could only be done with a rebuild. REORGANIZE 联机执行,并在后台进行,因此在进行该操作时可以继续进行查询。REORGANIZE is online and occurs in the background so queries can continue as the operation happens.

-- Uses AdventureWorks  
-- Defragment by physically removing rows that have been logically deleted from the table, and merging rowgroups.  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;  

E.E. 脱机重新生成聚集列存储索引Rebuild a clustered columnstore index offline

适用范围:SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x))

提示

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始并且在 Azure SQL DatabaseAzure SQL Database中,建议使用 ALTER INDEX REORGANIZE 而不是 ALTER INDEX REBUILD。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, we recommend using ALTER INDEX REORGANIZE instead of ALTER INDEX REBUILD.

备注

SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,REORGANIZE 仅用于将关闭的行组压缩到列存储中。In SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), REORGANIZE is only used to compress CLOSED rowgroups into the columnstore. 执行碎片整理操作以及将所有增量行组都强制到列存储中的唯一方法是重新生成索引。The only way to perform defragmentation operations and to force all delta rowgroups into the columnstore is to rebuild the index.

此示例演示如何重新生成聚集列存储索引以及将所有增量行组都强制到列存储中。This example shows how to rebuild a clustered columnstore index and force all delta rowgroups into the columnstore. 这个第一步将准备具有一个聚集列存储索引的表 FactInternetSales2 并且插入来自前四列的数据。This first step prepares a table FactInternetSales2 with a clustered columnstore index and inserts data from the first four columns.

-- Uses AdventureWorksDW  
  
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 ServerSQL Server 在关闭行组并且将数据移到列存储之前将等待添加更多的行。The results show there is one OPEN rowgroup, which means SQL ServerSQL Server will wait for more rows to be added before it closes the rowgroup and moves the data to the columnstore. 下一个语句重新生成聚集列存储索引,这会将所有行强制到列存储中。This next statement rebuilds the clustered columnstore index, which forces all rows into the columnstore.

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

SELECT 语句的结果表明行组被压缩 (COMPRESSED),这意味着行组的列段现在被压缩并且存储于列存储中。The results of the SELECT statement show the rowgroup is COMPRESSED, which means the column segments of the rowgroup are now compressed and stored in the columnstore.

F.F. 脱机重新生成聚集列存储索引的分区Rebuild a partition of a clustered columnstore index offline

适用对象SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x))

若要重新生成大型聚集列存储索引的分区,请使用带有分区选项的 ALTER INDEX REBUILD。To rebuild a partition of a large clustered columnstore index, use ALTER INDEX REBUILD with the partition option. 此示例重新生成分区 12。This example rebuilds partition 12. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,建议将 REBUILD 替换为 REORGANIZE。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), we recommend replacing REBUILD with REORGANIZE.

ALTER INDEX cci_fact3   
ON fact3  
REBUILD PARTITION = 12;  

G.G. 更改聚集列存储索引以使用存档压缩Change a clustered columstore index to use archival compression

不适用于:SQL Server 2012 (11.x)SQL Server 2012 (11.x)Does not apply to: SQL Server 2012 (11.x)SQL Server 2012 (11.x)

可以选择使用 COLUMNSTORE_ARCHIVE 数据压缩选项甚至进一步减少聚集列存储索引的大小。You can choose to reduce the size of a clustered columnstore index even further by using the COLUMNSTORE_ARCHIVE data compression option. 这对于要保留在较廉价存储上的较旧数据十分实用。This is practical for older data that you want to keep on cheaper storage. 建议仅对通常不访问的数据使用此方法,因为解压缩的速度低于正常 COLUMNSTORE 压缩。We recommend only using this on data that is not accessed often since decompress is slower than with the normal COLUMNSTORE compression.

下面的示例重新生成一个聚集列存储索引以便使用存档压缩,然后显示如何删除该存档压缩。The following example rebuilds a clustered columnstore index to use archival compression, and then shows how to remove the archival compression. 最后的结果将仅使用列存储压缩。The final result will use only columnstore compression.

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

示例:行存储索引Examples: Rowstore indexes

A.A. 重新生成索引Rebuilding an index

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库的 Employee 表中重新生成单个索引。The following example rebuilds a single index on the Employee table in the AdventureWorks2012AdventureWorks2012 database.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;  

B.B. 重新生成表的所有索引并指定选项Rebuilding all indexes on a table and specifying options

下面的示例指定了 ALL 关键字。The following example specifies the keyword ALL. 这将重新生成与 AdventureWorks2012AdventureWorks2012 数据库中的表 Production.Product 相关联的所有索引。This rebuilds all indexes associated with the table Production.Product in the AdventureWorks2012AdventureWorks2012 database. 其中指定了三个选项。Three options are specified.

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

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

下面的示例添加包含低优先级锁选项的 ONLINE 选项,并添加行压缩选项。The following example adds the ONLINE option including the low priority lock option, and adds the row compression option.

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

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.C. 通过 LOB 压缩重新组织索引Reorganizing an index with LOB compaction

下面的示例重新整理 AdventureWorks2012AdventureWorks2012 数据库中的单个聚集索引。The following example reorganizes a single clustered index in the AdventureWorks2012AdventureWorks2012 database. 因为该索引在叶级别包含 LOB 数据类型,所以该语句还会压缩所有包含该大型对象数据的页。Because the index contains a LOB data type in the leaf level, the statement also compacts all pages that contain the large object data. 注意,不需要指定 WITH (LOB_COMPACTION = ON) 选项,因为默认值为 ON。Note that specifying the WITH (LOB_COMPACTION = ON) option is not required because the default value is ON.

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

D.D. 设置索引的选项。Setting options on an index

下面的示例为 AdventureWorks2012AdventureWorks2012 数据库中的索引 AK_SalesOrderHeader_SalesOrderNumber 设置了几个选项。The following example sets several options on the index AK_SalesOrderHeader_SalesOrderNumber in the AdventureWorks2012AdventureWorks2012 database.

适用对象SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and SQL 数据库SQL Database.

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

E.E. 禁用索引。Disabling an index

下面的示例禁用了对 AdventureWorks2012AdventureWorks2012 数据库中的 Employee 表的非聚集索引。The following example disables a nonclustered index on the Employee table in the AdventureWorks2012AdventureWorks2012 database.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F.F. 禁用约束Disabling constraints

下面的示例通过禁用 AdventureWorks2012AdventureWorks2012 数据库中的 PRIMARY KEY 索引来禁用 PRIMARY KEY 约束。The following example disables a PRIMARY KEY constraint by disabling the PRIMARY KEY index in the AdventureWorks2012AdventureWorks2012 database. 自动禁用对基础表的 FOREIGN KEY 约束,并显示警告消息。The FOREIGN KEY constraint on the underlying table is automatically disabled and warning message is displayed.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;  

结果集返回此警告消息。The result set returns this warning message.

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.G. 启用约束Enabling constraints

下面的示例启用在示例 F 中禁用的 PRIMARY KEY 和 FOREIGN KEY 约束。The following example enables the PRIMARY KEY and FOREIGN KEY constraints that were disabled in Example F.

通过重新生成 PRIMARY KEY 索引启用 PRIMARY KEY 约束。The PRIMARY KEY constraint is enabled by rebuilding the PRIMARY KEY index.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;  

此时,将启用 FOREIGN KEY 约束。The FOREIGN KEY constraint is then enabled.

ALTER TABLE HumanResources.EmployeeDepartmentHistory  
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;  
GO  

H.H. 重新生成分区索引Rebuilding a partitioned index

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中重新生成一个分区索引为 5 的分区,分区号为 IX_TransactionHistory_TransactionDateThe following example rebuilds a single partition, partition number 5, of the partitioned index IX_TransactionHistory_TransactionDate in the AdventureWorks2012AdventureWorks2012 database. 分区 5 是联机重新生成的,并且对索引重新生成操作获取的每个锁分别应用低优先级锁的 10 分钟等待时间。Partition 5 is rebuilt online and the 10 minutes wait time for the low priority lock applies separately to every lock acquired by index rebuild operation. 如果在此时间无法获取锁来完成索引重新生成,重新生成操作语句就会中止。If during this time the lock cannot be obtained to complete index rebuild, the rebuild operation statement is aborted.

适用对象SQL ServerSQL Server(从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and SQL 数据库SQL Database.

-- 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.I. 更改索引的压缩设置Changing the compression setting of an index

下面的示例重新生成未分区行存储表的索引。The following example rebuilds an index on a nonpartitioned rowstore table.

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

有关其他数据压缩示例,请参阅数据压缩For additional data compression examples, see Data Compression.

J.J. 联机可恢复索引重新生成Online resumable index rebuild

适用对象SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and SQL 数据库SQL Database

下面的示例演示如何使用联机可恢复索引重新生成。The following examples show how to use online resumable index rebuild.

  1. 作为 MAXDOP=1 的可恢复操作执行联机索引重新生成。Execute an online index rebuild as resumable operation with MAXDOP=1.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;
    
  2. 在索引操作暂停之后再次执行相同命令(请参阅上文)会自动恢复索引重新生成操作。Executing the same command again (see above) after an index operation was paused, resumes automatically the index rebuild operation.

  3. 作为 MAX_DURATION 设置为 240 分钟的可恢复操作执行联机索引重新生成。Execute an online index rebuild as resumable operation with MAX_DURATION set to 240 minutes.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=240) ; 
    
  4. 暂停正在运行的可恢复联机索引重新生成。Pause a running resumable online index rebuild.

    ALTER INDEX test_idx on test_table PAUSE ;
    
  5. 对作为可恢复操作(为设置为 4 的 MAXDOP 指定新值)执行的索引重新生成,恢复联机索引重新生成。Resume an online index rebuild for an index rebuild that was executed as resumable operation specifying a new value for MAXDOP set to 4.

    ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP=4) ;
    
  6. 对作为可恢复操作执行的索引联机重新生成恢复联机索引重新生成操作。Resume an online index rebuild operation for an index online rebuild that was executed as resumable. 将 MAXDOP 设置为 2,将作为可恢复操作运行的索引的执行时间设置为 240 分钟,如果索引在锁上受阻,等待 10 分钟,在此之后终止所有阻塞程序。Set MAXDOP to 2, set the execution time for the index being running as resumable to 240 minutes and in case of an index being blocked on the lock wait 10 minutes and after that kill all blockers.

       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. 中止正在运行或暂停的可恢复索引重新生成操作。Abort resumable index rebuild operation which is running or paused.

    ALTER INDEX test_idx on test_table ABORT ;
    

另请参阅See Also

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
禁用索引和约束 Disable Indexes and Constraints
XML 索引 (SQL Server) XML Indexes (SQL Server)
联机执行索引操作 Perform Index Operations Online
重新组织和重新生成索引 Reorganize and Rebuild Indexes
sys.dm_db_index_physical_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)