CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

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

为表或视图创建相关索引。Creates a relational index on a table or view. 也称为行存储索引,因为它可能是聚集或非聚集的 B 树索引。Also called a rowstore index because it is either a clustered or nonclustered B-tree index. 可以在表中不存在数据时创建行存储索引。You can create a rowstore index before there is data in the table. 使用行存储索引提高查询性能,尤其是在查询从特定列中进行选择或需要按特定顺序对值进行排序时。Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

备注

SQL 数据仓库SQL Data Warehouse 并行数据仓库Parallel Data Warehouse 目前不支持唯一约束。and 并行数据仓库Parallel Data Warehouse currently do not support Unique constraints. 任何引用唯一约束的示例仅适用于 SQL ServerSQL ServerSQL 数据库SQL DatabaseAny examples referencing Unique Constraints are only applicable to SQL ServerSQL Server and SQL 数据库SQL Database.

提示

有关索引设计指南的信息,请参阅 SQL Server 索引设计指南For information on index design guidelines, refer to the SQL Server Index Design Guide.

简单示例:Simple examples:

-- Create a nonclustered index on a table or view
CREATE INDEX i1 ON t1 (col1);

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);

-- Syntax for SQL Server and Azure SQL Database
-- Create a nonclustered index with a unique constraint
-- on 3 columns and specify the sort order for each column
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

主要方案:Key scenario:

SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL 数据库SQL Database 开始,针对列存储索引使用非聚集索引以提高数据仓库查询性能。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL 数据库SQL Database, use a nonclustered index on a columnstore index to improve data warehousing query performance. 有关详细信息,请参阅列存储索引 - 数据仓库For more information, see Columnstore Indexes - Data Warehouse.

有关其他类型的索引,请参阅:For additional types of indexes, see:

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

语法Syntax

SQL Server 和 Azure SQL 数据库的语法Syntax for SQL Server and Azure SQL Database

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
  
[ ; ]
  
<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

<relational_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 }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | RESUMABLE = {ON | OF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE}
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::=
<partition_number_expression> TO <partition_number_expression>

后向兼容的关系索引Backward Compatible Relational Index

重要

SQL ServerSQL Server 的未来版本中,将删除此后向兼容的关系索引语法结构。The backward compatible relational index syntax structure will be removed in a future version of SQL ServerSQL Server. 请避免在新的开发工作中使用此语法结构,并计划修改当前使用此功能的应用程序。Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. 改用 <relational_index_option> 中指定的语法结构。Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

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

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Azure SQL 数据仓库和并行数据仓库的语法Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse


CREATE CLUSTERED COLUMNSTORE INDEX INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]


参数Arguments

UNIQUEUNIQUE
为表或视图创建唯一索引。Creates a unique index on a table or view. 唯一索引不允许两行具有相同的索引键值。A unique index is one in which no two rows are permitted to have the same index key value. 视图的聚集索引必须唯一。A clustered index on a view must be unique.

无论 IGNORE_DUP_KEY 是否设置为 ON,数据库引擎Database Engine都不允许为已包含重复值的列创建唯一索引。The 数据库引擎Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. 否则,数据库引擎Database Engine会显示错误消息。If this is tried, the 数据库引擎Database Engine displays an error message. 必须先删除重复值,然后才能为一列或多列创建唯一索引。Duplicate values must be removed before a unique index can be created on the column or columns. 唯一索引中使用的列应设置为 NOT NULL,因为在创建唯一索引时,会将多个 Null 值视为重复值。Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

CLUSTEREDCLUSTERED
创建索引时,键值的逻辑顺序决定表中对应行的物理顺序。Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. 聚集索引的底层(或称叶级别)包含该表的实际数据行。The bottom, or leaf, level of the clustered index contains the actual data rows of the table. 一个表或视图只允许同时有一个聚集索引。A table or view is allowed one clustered index at a time.

具有唯一聚集索引的视图称为索引视图。A view with a unique clustered index is called an indexed view. 为一个视图创建唯一聚集索引会在物理上具体化该视图。Creating a unique clustered index on a view physically materializes the view. 必须先为视图创建唯一聚集索引,然后才能为该视图定义其他索引。A unique clustered index must be created on a view before any other indexes can be defined on the same view. 有关详细信息,请参阅 创建索引视图For more information, see Create Indexed Views.

在创建任何非聚集索引之前创建聚集索引。Create the clustered index before creating any nonclustered indexes. 创建聚集索引时会重新生成表中现有的非聚集索引。Existing nonclustered indexes on tables are rebuilt when a clustered index is created.

如果没有指定 CLUSTERED,则创建非聚集索引。If CLUSTERED is not specified, a nonclustered index is created.

备注

因为按照定义,聚集索引的叶级别与其数据页相同,所以创建聚集索引和使用 ON partition_scheme_name 或 ON filegroup_name 子句实际上会将表从创建该表时所在的文件组高效地移到新的分区方案或文件组中 。Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. 对特定的文件组创建表或索引之前,应确认哪些文件组可用并且有足够的空间供索引使用。Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.

在某些情况下,创建聚集索引可以启用以前禁用的索引。In some cases creating a clustered index can enable previously disabled indexes. 有关详细信息,请参阅启用索引和约束禁用索引和约束For more information, see Enable Indexes and Constraints and Disable Indexes and Constraints.

NONCLUSTEREDNONCLUSTERED
创建一个指定表的逻辑排序的索引。Creates an index that specifies the logical ordering of a table. 对于非聚集索引,数据行的物理排序独立于索引排序。With a nonclustered index, the physical order of the data rows is independent of their indexed order.

无论是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建索引,还是使用 CREATE INDEX 显式创建索引,每个表都最多可包含 999 个非聚集索引。Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.

对于索引视图,只能为已定义唯一聚集索引的视图创建非聚集索引。For indexed views, nonclustered indexes can be created only on a view that has a unique clustered index already defined.

如果未另行指定,默认索引类型则为 NONCLUSTERED。If not otherwise specified, the default index type is NONCLUSTERED.

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.

column column
索引所基于的一列或多列。Is the column or columns on which the index is based. 指定两个或多个列名,可为指定列的组合值创建组合索引。Specify two or more column names to create a composite index on the combined values in the specified columns. 在 table_or_view_name 后的括号中,按排序优先级列出组合索引中要包括的列 。List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

一个组合索引键中最多可组合 32 列。Up to 32 columns can be combined into a single composite index key. 组合索引键中的所有列必须在同一个表或视图中。All the columns in a composite index key must be in the same table or view. 对于聚集索引,组合索引值允许的最大大小为 900 字节,对于非聚集索引则为 1,700 字节。The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. 对于 SQL 数据库SQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 以前的版本,此限制为 16 列和 900 字节。The limits are 16 columns and 900 bytes for versions before SQL 数据库SQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x).

无法将 ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml 或 image 大型对象 (LOB) 数据类型的列指定为索引的键列 。Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. 另外,即使 CREATE INDEX 语句中并未引用 ntext、text 或 image 列,视图定义中也不能包含这些列 。Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

如果 CLR 用户定义类型支持二进制排序,则可以为该类型的列创建索引。You can create indexes on CLR user-defined type columns if the type supports binary ordering. 另外,对于已定义为用户定义类型列的方法调用的计算列,只要这些方法标记为确定性方法且不执行数据访问操作,便可为该计算列创建索引。You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. 有关为 CLR 用户定义类型的列创建索引的详细信息,请参阅 CLR 用户定义类型For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

[ ASC | DESC ] [ ASC | DESC ]
确定特定索引列的升序或降序排序方向。Determines the ascending or descending sort direction for the particular index column. 默认值为 ASC。The default is ASC.

INCLUDE ( column [ , ... n ] ) INCLUDE (column [ ,... n ] )
指定要添加到非聚集索引的叶级别的非键列。Specifies the non-key columns to be added to the leaf level of the nonclustered index. 非聚集索引可以唯一,也可以不唯一。The nonclustered index can be unique or non-unique.

在 INCLUDE 列表中列名不能重复,且不能同时用于键列和非键列。Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. 如果对表定义了聚集索引,则非聚集索引始终包含聚集索引列。Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. 有关详细信息,请参阅 Create Indexes with Included ColumnsFor more information, see Create Indexes with Included Columns.

允许除 textntextimage之外的所有数据类型。All data types are allowed except text, ntext, and image. 如果指定的任一非键列属于 varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型,则必须脱机 (ONLINE = OFF) 创建或重新生成该索引 。The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

精确或不精确的确定性计算列都可以是包含列。Computed columns that are deterministic and either precise or imprecise can be included columns. 只要计算列的数据类型可以作为包含列,从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列就可以包含在非键列中 。Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. 有关详细信息,请参阅 计算列上的索引For more information, see Indexes on Computed Columns.

有关创建 XML 索引的信息,请参阅 CREATE XML INDEXFor information on creating an XML index, see CREATE XML INDEX.

WHERE <filter_predicate>WHERE <filter_predicate>
通过指定索引中要包含哪些行来创建筛选索引。Creates a filtered index by specifying which rows to include in the index. 筛选索引必须是对表的非聚集索引。The filtered index must be a nonclustered index on a table. 为筛选索引中的数据行创建筛选统计信息。Creates filtered statistics for the data rows in the filtered index.

筛选谓词使用简单比较逻辑且不能引用计算列、UDT 列、空间数据类型列或 hierarchyID 数据类型列。The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. 比较运算符不允许使用 NULL 文本的比较。Comparisons using NULL literals are not allowed with the comparison operators. 请改用 IS NULL 和 IS NOT NULL 运算符。Use the IS NULL and IS NOT NULL operators instead.

下面是一些 Production.BillOfMaterials 表筛选谓词示例:Here are some examples of filter predicates for the Production.BillOfMaterials table:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

筛选索引不适用于 XML 索引和全文检索。Filtered indexes do not apply to XML indexes and full-text indexes. 对于 UNIQUE 索引,仅选定的行必须具有唯一的索引值。For UNIQUE indexes, only the selected rows must have unique index values. 筛选索引不允许有 IGNORE_DUP_KEY 选项。Filtered indexes do not allow the IGNORE_DUP_KEY option.

ON partition_scheme_name ( column_name ) ON partition_scheme_name ( column_name )

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database

指定分区方案,该方案定义要将分区索引的分区映射到的文件组。Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. 须通过执行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME,使数据库中存在该分区方案。The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name 指定对已分区索引进行分区所依据的列 。column_name specifies the column against which a partitioned index will be partitioned. 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配 。This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name 不限于索引定义中的列 。column_name is not restricted to the columns in the index definition. 除了在对 UNIQUE 索引分区时,必须从用作唯一键的列中选择 column_name 外,还可以指定基表中的任何列 。Any column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. 通过此限制,数据库引擎Database Engine可验证单个分区中的键值唯一性。This restriction allows the 数据库引擎Database Engine to verify uniqueness of key values within a single partition only.

备注

在对非唯一的聚集索引进行分区时,如果尚未指定分区依据列,则默认情况下数据库引擎Database Engine将在聚集索引键列表中添加分区依据列。When you partition a non-unique, clustered index, the 数据库引擎Database Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. 在对非唯一的非聚集索引进行分区时,如果尚未指定分区依据列,则数据库引擎Database Engine会添加分区依据列作为索引的非键(包含)列。When partitioning a non-unique, nonclustered index, the 数据库引擎Database Engine adds the partitioning column as a non-key (included) column of the index, if it is not already specified.

如果未指定 partition_scheme_name 或 filegroup 且该表已分区,则索引会与基础表使用相同分区依据列并被放入同一分区方案中 。If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

备注

您不能对 XML 索引指定分区方案。You cannot specify a partitioning scheme on an XML index. 如果基表已分区,则 XML 索引与该表使用相同的分区方案。If the base table is partitioned, the XML index uses the same partition scheme as the table.

有关分区索引的详细信息,请参阅已分区表和已分区索引For more information about partitioning indexes, Partitioned Tables and Indexes.

ON filegroup_name ON filegroup_name

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017)

为指定文件组创建指定索引。Creates the specified index on the specified filegroup. 如果未指定位置且表或视图尚未分区,则索引将与基础表或视图使用相同的文件组。If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. 该文件组必须已存在。The filegroup must already exist.

ON "default" ON " default "

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database

在表或视图所在的文件组或分区方案上创建指定索引。Creates the specified index on the same filegroup or partition scheme as the table or view.

在此上下文中,“default”一词不是关键字。The term default, in this context, is not a keyword. 而是默认文件组的标识符,并且必须进行分隔,如 ON "default" 或 ON [default] 中所示 。It is an identifier for the default filegroup and must be delimited, as in ON " default " or ON [ default ]. 如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

备注

“default”不表示 CREATE INDEX 上下文中的数据库默认文件组。"default" does not indicate the database default filegroup in the context of CREATE INDEX. 这与 CREATE TABLE 不同,在 CREATE TABLE 中,“default”会在数据库默认文件组上找到表。This differs from CREATE TABLE, where "default" locates the table on the database default filegroup.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017)

在创建聚集索引时,指定表的 FILESTREAM 数据的位置。Specifies the placement of FILESTREAM data for the table when a clustered index is created. FILESTREAM_ON 子句用于将 FILESTREAM 数据移动到不同的 FILESTREAM 文件组或分区方案。The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.

filestream_filegroup_name 是 FILESTREAM 文件组的名称 。filestream_filegroup_name is the name of a FILESTREAM filegroup. 该文件组须包含一个使用 CREATE DATABASEALTER DATABASE 语句为该文件组定义的文件;否则,会引发错误。The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.

如果表已分区,则必须包含 FILESTREAM_ON 子句并且必须指定 FILESTREAM 文件组的分区方案,且此分区方案需使用与该表分区方案相同的分区函数和分区列。If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. 否则将引发错误。Otherwise, an error is raised.

如果该表未分区,则无法对 FILESTREAM 列分区。If the table is not partitioned, the FILESTREAM column cannot be partitioned. 该表的 FILESTREAM 数据必须存储在一个由 FILESTREAM_ON 子句指定的文件组中。FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.

如果创建的是聚集索引且该表不包含 FILESTREAM 列,则可在 CREATE INDEX 语句中指定 FILESTREAM_ON NULLFILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

有关详细信息,请参阅 FILESTREAM (SQL Server)For more information, see FILESTREAM (SQL Server).

<object>::=<object>::=

要为其建立索引的完全限定对象或非完全限定对象。Is the fully qualified or nonfully qualified object to be indexed.

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

schema_name schema_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 to be indexed.

必须使用 SCHEMABINDING 定义视图,才能为视图创建索引。The view must be defined with SCHEMABINDING to create an index on it. 必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。A unique clustered index must be created on a view before any nonclustered index is created. 有关索引视图的详细信息,请参阅“备注”部分。For more information about indexed views, see the Remarks section.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,该对象可以是聚集列存储索引存储的表。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the object can be a table stored with a clustered columnstore index.

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

<relational_index_option>::= <relational_index_option>::=
指定创建索引时要使用的选项。Specifies the options to use when you create the index.

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

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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.

OFF 或未指定 fillfactor OFF or fillfactor is not specified
考虑到中间级页上的键集,将中间级页填充到接近其容量的程度,以留出足够的空间,使之至少能够容纳索引的最大的一行。The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. 如果为 FILLFACTOR 指定的百分比不够大,无法容纳一行,数据库引擎Database Engine将在内部覆盖该百分比以允许最小值。If the percentage specified for FILLFACTOR is not large enough to allow for one row, the 数据库引擎Database Engine internally overrides the percentage to allow for the minimum. 无论 fillfactor 的值有多小,中间级索引页上的行数永远都不会小于两行 。The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

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

FILLFACTOR =fillfactor FILLFACTOR =fillfactor

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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 rebuild. fillfactor 必须是 1 到 100 之间的整数 。fillfactor must be an integer value from 1 to 100. 如果 fillfactor 为 100,数据库引擎Database Engine会创建完全填充叶级页的索引 。If fillfactor is 100, the 数据库引擎Database Engine creates indexes with leaf pages filled to capacity.

FILLFACTOR 设置仅在创建或重新生成索引时应用。The FILLFACTOR setting applies only when the index is created or rebuilt. 数据库引擎Database Engine并不会在页中动态保持指定的可用空间百分比。The 数据库引擎Database Engine does not dynamically keep the specified percentage of empty space in the pages. 若要查看填充因子设置,请使用 sys.indexes 目录视图。To view the fill factor setting, use the sys.indexes catalog view.

重要

使用低于 100 的 FILLFACTOR 值创建聚集索引会影响数据占用的存储空间量,因为数据库引擎Database Engine在创建聚集索引时会重新分布数据。Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the 数据库引擎Database Engine redistributes the data when it creates the clustered index.

有关详细信息,请参阅 为索引指定填充因子For more information, see Specify Fill Factor for an Index.

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

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database

指定是否在 tempdb 中存储临时排序结果 。Specifies whether to store temporary sort results in tempdb. 默认设置为 OFF,但“超大规模 Azure SQL 数据库”除外。对于“超大规模”中的所有索引生成操作,无论指定什么选项,SORT_IN_TEMPDB 始终为 ON,除非使用可恢复索引重新生成。The default is OFF except for Azure SQL Database Hyperscale.For all index build operations in Hyperscale, SORT_IN_TEMPDB is always ON, regardless of the option specified unless resumable index rebuild is used.

ONON
在 tempdb 中存储用于生成索引的中间排序结果 。The intermediate sort results that are used to build the index are stored in tempdb. 如果 tempdb 与用户数据库不在同一组磁盘上,就可缩短创建索引所需的时间 。This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. 但是,这会增加索引生成期间所使用的磁盘空间量。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.

除在用户数据库中创建索引所需的空间外,tempdb 还必须有大约相同的额外空间来存储中间排序结果 。In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. 有关详细信息,请参阅用于索引的 SORT_IN_TEMPDB 选项For more information, see SORT_IN_TEMPDB Option For Indexes.

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

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. 当执行 CREATE INDEXALTER INDEXUPDATE 时,该选项无效。The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. 默认为 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 involving the table.

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

STATISTICS_INCREMENTAL = { ON | OFF } STATISTICS_INCREMENTAL = { ON | OFF }

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

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

DROP_EXISTING = { ON | OFF } DROP_EXISTING = { ON | OFF }
一个选项,用于删除并重新生成具有已修改列规范的现有聚集或非聚集索引,同时为该索引设置相同的名称。Is an option to drop and rebuild the existing clustered or nonclustered index with modified column specifications, and keep the same name for the index. 默认为 OFF。The default is OFF.

ONON
指定删除并重新生成现有索引,该索引必须与 index_name 参数具有相同名称 。Specifies to drop and rebuild the existing index, which must have the same name as the parameter index_name.

OFFOFF
指定不删除和重新生成现有索引。Specifies not to drop and rebuild the existing index. 如果指定的索引名称已存在,SQL Server 将显示错误。SQL Server displays an error if the specified index name already exists.

使用 DROP_EXISTING 可做如下更改:With DROP_EXISTING, you can change:

  • 将非聚集行存储索引更改为聚集行存储索引。A nonclustered rowstore index to a clustered rowstore index.

使用 DROP_EXISTING 不能做如下更改:With DROP_EXISTING, you cannot change:

  • 将聚集行存储索引更改为非聚集行存储索引。A clustered rowstore index to a nonclustered rowstore index.
  • 将聚集列存储索引更改为任何类型的行存储索引。A clustered columnstore index to any type of rowstore index.

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

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

重要

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

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 enables queries or updates to the underlying table and indexes to proceed. 操作开始时,在很短的时间内对源对象持有共享 (S) 锁。At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. 操作结束时,如果创建非聚集索引,将在短期内获取对源的 S(共享)锁;当联机创建或删除聚集索引时,以及重新生成聚集或非聚集索引时,将在短期内获取 SCH-M(架构修改)锁。At the end of the operation, for a short period of time, an S (Shared) lock is acquired 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 and 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. 创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。An offline index operation that creates, rebuilds, or drops a clustered 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.

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

可以联机创建索引(包括全局临时表中的索引),但以下情况除外:Indexes, including indexes on global temp tables, can be created online except for the following cases:

  • XML 索引XML index
  • 对本地临时表的索引Index on a local temp table
  • 视图的初始唯一聚集索引Initial unique clustered index on a view
  • 已禁用的聚集索引Disabled clustered indexes
  • 列存储索引Columnstore indexes
  • 聚集索引,前提是基础表包含 LOB 数据类型(image 、ntext 、text )和空间数据类型Clustered index, if the underlying table contains LOB data types (image, ntext, text) and spatial data types
  • varchar(max) 和 varbinary(max) 列不能是索引的一部分 。varchar(max) and varbinary(max) columns cannot be part of an index. SQL ServerSQL Server(自 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 起)和 Azure SQL DatabaseAzure SQL Database 中,当表包含 varchar(max) 或 varbinary(max) 列时,可以使用 ONLINE 选项生成或重新生成包含其他列的聚集索引。In SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database, when a table contains varchar(max) or varbinary(max) columns, a clustered index containing other columns can be built or rebuilt using the ONLINE option. 当基表包含 varchar(max) 或 varbinary(max) 列时,Azure SQL DatabaseAzure SQL Database 不允许使用 ONLINE 选项Azure SQL DatabaseAzure SQL Database does not permit the ONLINE option when the base table contains varchar(max) or varbinary(max) columns

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

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

适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL Database(公共预览版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database (public preview)

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

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

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

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

适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL Database(公共预览版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database (public preview)

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

重要

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

备注

列存储索引不支持可恢复联机索引重新生成。Resumable online index rebuilds are not supported on columnstore indexes.

ALLOW_ROW_LOCKS = { ON | OFF } ALLOW_ROW_LOCKS = { ON | OFF }
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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 ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database

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

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

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

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

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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.

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 or fewer based on the current system workload.

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.

备注

并非在 MicrosoftMicrosoftSQL ServerSQL Server 的每个版本中均支持并行索引操作。Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能的列表,请参阅 SQL Server 2016 的版本和支持的功能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 and Editions and Supported Features for SQL Server 2017.

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.

ROWROW
使用行压缩来压缩索引或指定的分区。Index or specified partitions are compressed by using row compression.

PAGEPAGE
使用页压缩来压缩索引或指定的分区。Index or specified partitions are compressed by using page compression.

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

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] ) ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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, for example: 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)
);

RemarksRemarks

CREATE INDEX 语句同其他查询一样优化。The CREATE INDEX statement is optimized like any other query. 为了节省 I/O 操作,查询处理器可以选择扫描另一个索引,而不是执行表扫描。To save on I/O operations, the query processor may choose to scan another index instead of performing a table scan. 在某些情况下,可不必执行排序操作。The sort operation may be eliminated in some situations. 在多处理器计算机上,CREATE INDEX 可按照与其他查询相同的方式,使用多个处理器执行与创建索引相关的扫描和排序操作。On multiprocessor computers CREATE INDEX can use more processors to perform the scan and sort operations associated with creating the index, in the same way as other queries do. 有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

如果数据库恢复模型被设置为大容量日志模型或简单模型,则可以记录最少的创建索引操作。The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

可以为临时表创建索引。Indexes can be created on a temporary table. 在删除表或结束会话时,将删除索引。When the table is dropped or the session ends, the indexes are dropped.

创建主键时,聚集索引可以基于表变量。A clustered index can be built on a table variable when a Primary Key is created. 在查询完成或会话结束时,将删除索引。When the query completes or the session ends, the index is dropped.

索引支持扩展属性。Indexes support extended properties.

聚集索引Clustered Indexes

对表(堆)创建聚集索引或删除和重新创建现有聚集索引时,要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. 有关聚集索引的详细信息,请参阅创建聚集索引SQL Server 索引体系结构和设计指南For more information about clustered indexes, see Create Clustered Indexes and the SQL Server Index Architecture and Design Guide.

非聚集索引Nonclustered Indexes

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始和在 Azure SQL DatabaseAzure SQL Database 中,可以为作为聚集列存储索引进行存储的表创建非聚集索引。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, you can create a nonclustered index on a table stored as a clustered columnstore index. 如果首先为作为堆或聚集索引进行存储的表创建非聚集索引,当后期将该表转换为聚集列存储索引时,该索引将会保留。If you first create a nonclustered index on a table stored as a heap or clustered index, the index will persist if you later convert the table to a clustered columnstore index. 重新生成聚集列存储索引也不需要删除非聚集索引。It is also not necessary to drop the nonclustered index when you rebuild the clustered columnstore index.

限制和局限:Limitations and Restrictions:

  • 为作为聚集列存储索引进行存储的表创建非聚集索引时,FILESTREAM_ON 选项无效。The FILESTREAM_ON option is not valid when you create a nonclustered index on a table stored as a clustered columnstore index.

唯一索引Unique Indexes

如果存在唯一索引,数据库引擎Database Engine会在每次插入操作添加数据时检查重复值。When a unique index exists, the 数据库引擎Database Engine checks for duplicate values each time data is added by a insert operations. 可生成重复键值的插入操作将被回滚,同时数据库引擎Database Engine显示错误消息。Insert operations that would generate duplicate key values are rolled back, and the 数据库引擎Database Engine displays an error message. 即使插入操作更改多行但只导致出现一个重复值时,也是如此。This is true even if the insert operation changes many rows but causes only one duplicate. 如果在存在唯一索引并且 IGNORE_DUP_KEY 子句设置为 ON 的情况下输入数据,则只有违反 UNIQUE 索引的行才会失败。If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is set to ON, only the rows violating the UNIQUE index fail.

分区索引Partitioned Indexes

创建和维护分区索引的方式与已分区表相同,但与普通索引一样,将分区索引作为单独数据库对象来进行处理。Partitioned indexes are created and maintained in a similar manner to partitioned tables, but like ordinary indexes, they are handled as separate database objects. 可以在未分区的表中使用分区索引,也可以在已分区表中使用未分区索引。You can have a partitioned index on a table that is not partitioned, and you can have a nonpartitioned index on a table that is partitioned.

如果要对已分区表创建索引,并且不指定用于放置该索引的文件组,则会按照与基础表相同的方式为该索引分区。If you are creating an index on a partitioned table, and do not specify a filegroup on which to place the index, the index is partitioned in the same manner as the underlying table. 这是因为在默认情况下,索引与其基础表放在同一文件组中,并且对应使用相同分区依据列的相同分区方案中的已分区表。This is because indexes, by default, are placed on the same filegroups as their underlying tables, and for a partitioned table in the same partition scheme that uses the same partitioning columns. 当索引与表使用同一个分区方案和分区列时,索引将与表对齐 。When the index uses the same partition scheme and partitioning column as the table, the index is aligned with the table.

警告

对超过 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.

在对非唯一的聚集索引分区时,如果尚未指定分区依据列,则默认情况下数据库引擎Database Engine将在聚集索引键列表中添加任意分区依据列。When partitioning a non-unique, clustered index, the 数据库引擎Database Engine by default adds any partitioning columns to the list of clustered index keys, if not already specified.

可以使用与为表创建索引时相同的方式,为已分区表创建索引视图。Indexed views can be created on partitioned tables in the same manner as indexes on tables. 有关聚集索引的详细信息,请参阅分区表和索引以及 SQL Server 索引体系结构和设计指南For more information about partitioned indexes, see Partitioned Tables and Indexes and the SQL Server Index Architecture and Design Guide.

SQL Server 2017SQL Server 2017 中,当创建或重新生成已分区索引时,将通过扫描表中的所有行来创建统计信息。In SQL Server 2017SQL Server 2017, 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 statistics. 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息,请使用 CREATE STATISTICSUPDATE 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.

筛选索引Filtered Indexes

筛选索引是一种经过优化的非聚集索引,适用于从表中选择少数行的查询。A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. 筛选索引使用筛选谓词对表中的部分数据进行索引。It uses a filter predicate to index a portion of the data in the table. 设计良好的筛选索引可以提高查询性能,降低存储成本和维护成本。A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

筛选索引所需的 SET 选项Required SET Options for Filtered Indexes

如果下列任何条件成立,则需要“必需的值”列中的 SET 选项:The SET options in the Required Value column are required whenever any of the following conditions occur:

  • 创建筛选索引。Create a filtered index.

  • INSERT、UPDATE、DELETE 或 MERGE 操作修改筛选索引中的数据。INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

  • 查询优化器使用该筛选索引生成查询计划。The filtered index is used by the query optimizer to produce the query plan.

    SET 选项SET options 必需的值Required value 默认服务器值Default server value 默认Default

    OLE DB 和 ODBC 值OLE DB and ODBC value
    默认Default

    DB-Library 值DB-Library value
    ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGS*ANSI_WARNINGS* ONON ONON ONON OFFOFF
    ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF
    • 当数据库兼容级别设置为 90 或更高时,如果将 ANSI_WARNINGS 设置为 ON,则将使 ARITHABORT 隐式设置为 ON。Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. 如果数据库兼容性级别设置为 80 或更低,则必须将 ARITHABORT 选项显式设置为 ON。If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

如果 SET 选项不正确,则可能会出现以下情况:If the SET options are incorrect, the following conditions can occur:

  • 不会创建筛选索引。The filtered index is not created.
  • 数据库引擎Database Engine生成错误并回滚对索引中的数据进行更改的 INSERT、UPDATE、DELETE 或 MERGE 语句。The 数据库引擎Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.
  • 查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

有关筛选索引的详细信息,请参阅创建筛选索引SQL Server 索引体系结构和设计指南For more information about Filtered Indexes, see Create Filtered Indexes and the SQL Server Index Architecture and Design Guide.

空间索引Spatial Indexes

有关空间索引的信息,请参阅 CREATE SPATIAL INDEX空间索引概述For information about spatial indexes, see CREATE SPATIAL INDEX and Spatial Indexes Overview.

XML 索引XML Indexes

有关 XML 索引的信息,请参阅 CREATE XML INDEXXML 索引 (SQL Server)For information about XML indexes see, CREATE XML INDEX and XML Indexes (SQL Server).

索引键大小Index Key Size

对于聚集索引,索引键的最大大小为 900 字节,对于非聚集索引为 1700 字节。The maximum size for an index key is 900 bytes for a clustered index and 1,700 bytes for a nonclustered index. (对于 SQL 数据库SQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 之前的版本,此限制始终为 900 字节。)如果创建索引时,varchar 列中的现有数据未超过限制,则可以对这些列创建超过字节限制的索引;但是,以后在这些列上执行会导致总大小超过该限制的插入或更新操作时将失败 。(Before SQL 数据库SQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x) the limit was always 900 bytes.) Indexes on varchar columns that exceed the byte limit can be created if the existing data in the columns do not exceed the limit at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than the limit will fail. 聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varcharr 列。The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. 如果对 varchar 列创建了聚集索引,并且 IN_ROW_DATA 分配单元中存在现有数据,则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail.

非聚集索引可以在索引的叶级别包含非键列。Nonclustered indexes can include non-key columns in the leaf level of the index. 计算索引键大小时,数据库引擎Database Engine不考虑这些列。These columns are not considered by the 数据库引擎Database Engine when calculating the index key size . 有关详细信息,请参阅创建带有包含列的索引SQL Server 索引体系结构和设计指南For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

备注

在对表进行分区时,如果分区键列尚未出现在非唯一聚集索引中时,它们将由数据库引擎Database Engine添加到索引中。When tables are partitioned, if the partitioning key columns are not already present in a non-unique clustered index, they are added to the index by the 数据库引擎Database Engine. 索引列的合并后的大小(不将包含列计算在内)加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节。The combined size of the indexed columns (not counting included columns), plus any added partitioning columns cannot exceed 1800 bytes in a non-unique clustered index.

计算列Computed Columns

可以对计算列创建索引。Indexes can be created on computed columns. 此外,计算列可以具有 PERSISTED 属性。In addition, computed columns can have the property PERSISTED. 这意味着 数据库引擎Database Engine 在表中存储计算值,并且在计算列所依赖的任何其他列发生更新时更新这些值。This means that the 数据库引擎Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. 如果 数据库引擎Database Engine 对列创建了索引并且该索引由某查询引用,则会使用这些持久值。The 数据库引擎Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

若要对计算列建立索引则该计算列必须具有确定性并精确。To index a computed column, the computed column must deterministic and precise. 但是,使用 PERSISTED 属性会将可建立索引的计算列类型扩展为包含以下类型:However, using the PERSISTED property expands the type of indexable computed columns to include:

  • 基于 Transact-SQLTransact-SQL 和 CLR 函数以及由用户标记为确定性的 CLR 用户定义类型方法的计算列。Computed columns based on Transact-SQLTransact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.
  • 基于数据库引擎Database Engine定义为确定性但不精确的表达式的计算列。Computed columns based on expressions that are deterministic as defined by the 数据库引擎Database Engine but imprecise.

持久化计算列需要将以下 SET 选项设置为在上一部分筛选索引所需的 SET 选项中显示的内容。Persisted computed columns require the following SET options to be set as shown in the previous section Required SET Options for Filtered Indexes.

UNIQUE 或 PRIMARY KEY 约束只要满足所有索引条件,就可以包含计算列。The UNIQUE or PRIMARY KEY constraint can contain a computed column as long as it satisfies all conditions for indexing. 具体来说,计算列必须具有确定性并精确,或者具有确定性并持久化。Specifically, the computed column must be deterministic and precise or deterministic and persisted. 有关确定性的详细信息,请参阅确定性函数和不确定性函数For more information about determinism, see Deterministic and Nondeterministic Functions.

只要计算列的数据类型可以作为索引键列或非键列,从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列(作为键列或包含非键列)上就可以创建索引 。Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed either as a key or included non-key column as long as the computed column data type is allowable as an index key column or non-key column. 例如,不能对 xml 计算列创建主 XML 索引 。For example, you cannot create a primary XML index on a computed xml column. 如果索引键大小超过 900 字节,会显示一条警告消息。If the index key size exceeds 900 bytes, a warning message is displayed.

对计算列创建索引可能导致之前正常运行的插入或更新操作失败。Creating an index on a computed column may cause the failure of an insert or update operation that previously worked. 当计算列导致算术错误时可能产生这样的失败。Such a failure may take place when the computed column results in arithmetic error. 例如,虽然下表中的计算列 c 将导致算术错误,但是 INSERT 语句仍有效。For example, in the following table, although computed column c results in an arithmetic error, the INSERT statement works.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

相反,如果创建表之后对计算列 c 创建索引,则上述 INSERT 语句将失败。If, instead, after creating the table, you create an index on computed column c, the same INSERT statement will now fail.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

有关详细信息,请参阅 计算列上的索引For more information, see Indexes on Computed Columns.

索引中的包含列Included Columns in Indexes

可以将非键列(称为包含列)添加到非聚集索引的叶级别,从而通过涵盖查询来提高查询性能。Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. 也就是说,查询中引用的所有列都作为键列或非键列包含在索引中。That is, all columns referenced in the query are included in the index as either key or non-key columns. 这样,查询优化器可以通过索引扫描找到所需的全部信息,而无需访问表或聚集索引数据。This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. 有关详细信息,请参阅创建带有包含列的索引SQL Server 索引体系结构和设计指南For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

指定索引选项Specifying Index Options

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中引入了新的索引选项,还修改了指定选项的方式。introduced new index options and also modifies the way in which options are specified. 在向后兼容的语法中,WITH option_name 等效于 WITH ( <option_name> = ON ) 。In backward compatible syntax, WITH option_name is equivalent to WITH ( <option_name> = ON ). 在设置索引选项时,下列规则适用:When you set index options, the following rules apply:

  • 新的索引选项只能使用 WITH (option_name = ON | OFF ) 指定。New index options can only be specified by using WITH (option_name = ON | OFF).
  • 指定选项时不能在同一语句中同时使用向后兼容语法和新语法。Options cannot be specified by using both the backward compatible and new syntax in the same statement. 例如,指定 WITH (DROP_EXISTING, ONLINE = ON) 会导致语句失败 。For example, specifying WITH (DROP_EXISTING, ONLINE = ON) causes the statement to fail.
  • 在创建 XML 索引时,必须使用 WITH (option_name= ON | OFF) 指定选项 。When you create an XML index, the options must be specified by using WITH (option_name= ON | OFF).

DROP_EXISTING 子句DROP_EXISTING Clause

可使用 DROP_EXISTING 子句重新生成索引、添加或删除列、修改选项、修改列排序顺序或更改分区方案或文件组。You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.

如果索引强制 PRIMARY KEY 或 UNIQUE 约束,且索引定义没有任何改变,则会删除并重新创建该索引并保留现有约束。If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. 不过,如果索引定义已改变,则该语句将失败。However, if the index definition is altered the statement fails. 若要更改 PRIMARY KEY 或 UNIQUE 约束的定义,请删除该约束并添加具有新定义的约束。To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

为已经具有非聚集索引的表重建聚集索引时(使用相同或不同的键集),DROP_EXISTING 可以提高性能。DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has nonclustered indexes. DROP_EXISTING 代替先对旧的聚集索引执行 DROP INDEX 语句,然后再对新的聚集索引执行 CREATE INDEX 语句的过程。DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. 而是将重新生成一次非聚集索引,之后仅在索引定义已更改时再重新生成。The nonclustered indexes are rebuilt once, and then only if the index definition has changed. 如果索引定义与原始索引具有相同的索引名称、键列和分区列、唯一性属性以及排序顺序,则 DROP_EXISTING 子句不会重新生成非聚集索引。The DROP_EXISTING clause does not rebuild the nonclustered indexes when the index definition has the same index name, key and partition columns, uniqueness attribute, and sort order as the original index.

无论是否重新生成非聚集索引,它们都将始终保留在其原始文件组或分区方案中,并使用原始的分区函数。Whether the nonclustered indexes are rebuilt or not, they always remain in their original filegroups or partition schemes and use the original partition functions. 如果聚集索引被重新生成到其他文件组或分区方案中,这些非聚集索引不会通过移动来与聚集索引的新位置保持一致。If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index. 所以,即使非聚集索引以前与聚集索引对齐,现在可能也不再与其对齐。Therefore, even the nonclustered indexes previously aligned with the clustered index, they may no longer be aligned with it. 有关已分区索引的详细信息,请参阅已分区表和已分区索引For more information about partitioned index alignment, see Partitioned Tables and Indexes.

如果以相同顺序使用相同索引键列,且具有相同升序和降序,则 DROP_EXISTING 子句不会重新对数据排序,除非索引语句指定非聚集索引且 ONLINE 选项设置为 OFF。The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. 如果聚集索引被禁用,则必须在 ONLINE 设置为 OFF 的情况下执行 CREATE INDEX WITH DROP_EXISTING 操作。If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. 如果非聚集索引被禁用且不与禁用的聚集索引关联,则可以在 ONLINE 设置为 OFF 或 ON 时执行 CREATE INDEX WITH DROP_EXISTING 操作。If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.

备注

删除或重新生成具有 128 个或更多区数的索引时,数据库引擎Database Engine会将实际页释放及其关联的锁推迟到事务提交后。When indexes with 128 extents or more are dropped or rebuilt, the 数据库引擎Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

ONLINE 选项ONLINE Option

下列指南适用于联机执行索引操作:The following guidelines apply for performing index operations online:

  • 不能在执行联机索引操作的过程中更改、截断或删除基础表。The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
  • 索引操作期间需要额外的临时磁盘空间。Additional temporary disk space is required during the index operation.
  • 可以对分区索引以及包含持久性计算列或包含列的索引执行联机操作。Online operations can be performed on partitioned indexes and indexes that contain persisted computed columns, or included columns.

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

可恢复索引操作Resumable index operations

适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL Database(公共预览版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database (public preview)

下列指南适用于可恢复索引操作:The following guidelines apply for resumable index operations:

  • 联机索引创建可使用 RESUMABLE = ON 选项指定为可恢复。Online index create 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 选项支持 MAX_DURATION 选项。MAX_DURATION option is only supported for RESUMABLE = ON option.
  • 用于 RESUMABLE 选项的 MAX_DURATION 为生成的索引指定时间间隔。MAX_DURATION for RESUMABLE option specifies the time interval for an index being built. 使用此时间之后,索引生成会暂停或完成其执行。Once this time is used the index build is either paused or it completes its execution. 由用户确定何时可以恢复暂停的索引的生成。User decides when a build 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.
  • 若要立即暂停索引操作,则可以停止 (Ctrl-C) 正在进行的命令,执行 ALTER INDEX PAUSE 命令或执行 KILL <session_id> 命令。To pause immediately the index operation, you can stop (Ctrl-C) the ongoing command, execute the ALTER INDEX PAUSE command, or execute the KILL <session_id> command. 暂停命令之后,可以使用 ALTER INDEX 命令恢复它。Once the command is paused, it can be resumed using ALTER INDEX command.
  • 重新执行原始 CREATE INDEX 语句的可恢复索引,会自动恢复暂停的索引创建操作。Re-executing the original CREATE INDEX statement for resumable index, automatically resumes a paused index create operation.
  • 可恢复索引不支持 SORT_IN_TEMPDB = ON 选项。The SORT_IN_TEMPDB = ON option is not supported for resumable index.
  • 具有 RESUMABLE = ON 的 DDL 命令无法在显式事务(不能属于 begin 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).
  • 若要恢复/中止索引创建/重新生成,请使用 ALTER INDEX T-SQL 语法To resume/abort an index create/rebuild, use the ALTER INDEX T-SQL syntax

备注

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.

若要指示索引创建作为可恢复操作执行并检查其当前执行状态,请参阅 sys.index_resumable_operationsTo indicate that an index create is executed as resumable operation and to check its current execution state, see sys.index_resumable_operations.

ResourcesResources

可恢复联机索引创建操作需要以下资源:The following resources are required for resumable online index create operation:

  • 使索引保持生成所需的附加空间,包括索引暂停的时间Additional space required to keep the index being built, including the time when index is being paused
  • 排序阶段中的额外日志吞吐量。Additional log throughput during the sorting phase. 与常规联机索引创建相比,可恢复索引的总体日志空间使用率较低,并允许在此操作期间进行日志截断。The overall log space usage for resumable index is less compared to regular online index create and allows log truncation during this operation.
  • 阻止任何 DDL 修改的 DDL 状态A DDL state preventing any DDL modification
  • 在操作期间暂停时和运行操作时都会阻止对内置索引进行虚影清除。Ghost cleanup is blocked on the in-build index for the duration of the operation both while paused and while the operation is running.

当前功能限制Current functional limitations

对于可恢复索引创建操作会禁用以下功能:The following functionality is disabled for resumable index create operations:

  • 可恢复联机索引创建操作暂停后,不能更改 MAXDOP 的初始值After a resumable online index create operation is paused, the initial value of MAXDOP cannot be changed

  • 创建一个索引,其中包含:Create an index that contains:

    • 已计算或 TIMESTAMP 列作为键列Computed or TIMESTAMP column(s) as key columns
    • LOB 列作为包含列的可恢复索引创建LOB column as included column for resumable index create
    • 筛选索引Filtered index

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

如果 ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCK = ON,在访问索引时允许使用行级别、页级别和表级别锁定。When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when accessing 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 = OFFALLOW_PAGE_LOCK = OFF,在访问索引时仅允许使用表级别锁定。When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when accessing the index.

顺序键Sequential Keys

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

最后一页插入争用是在以下情况下发生的常见性能问题:当大量并发线程尝试将行插入包含顺序键的索引时。Last-page insert contention is a common performance problem that occurs when a large number of concurrent threads attempt to insert rows into an index with a sequential key. 如果前导键列包含始终增加(或减少)的值(如标识列),或包含默认为当前日期/时间的日期,索引就会被视为顺序索引。An index is considered sequential when the leading key column contains values that are always increasing (or decreasing), such as an identity column or a date that defaults to the current date/time. 由于键是按顺序插入,因此所有新行都会插入到索引结构的末尾处,即位于同一页面上。Because the keys being inserted are sequential, all new rows will be inserted at the end of the index structure - in other words, on the same page. 这会导致内存中出现页面争用,可观察到多个线程在 PAGELATCH_EX 上等待相关页面。This leads to contention for the page in memory which can be observed as several threads waiting on PAGELATCH_EX for the page in question.

启用 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引选项可以在数据库引擎内启用优化,有助于提高索引中高并发插入的吞吐量。Turning on the OPTIMIZE_FOR_SEQUENTIAL_KEY index option enables an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. 它适用于因包含顺序键而容易发生最后一页插入争用的索引,但可能也有助于在 B 树索引结构的其他区域中有作用点的索引。It is intended for indexes that have a sequential key and thus are prone to last-page insert contention, but it may also help with indexes that have hot spots in other areas of the B-Tree index structure.

查看索引信息Viewing Index Information

若要返回有关索引的信息,可以使用目录视图、系统函数和系统存储过程。To return information about indexes, you can use catalog views, system functions, and system stored procedures.

Data CompressionData Compression

数据压缩会在数据压缩主题中进行介绍。Data compression is described in the topic Data Compression. 以下是要考虑的关键点:The following are key points to consider:

  • 通过压缩可将更多的行存储在页上,但不能更改最大行大小。Compression can allow more rows to be stored on a page, but does not change the maximum row size.
  • 对索引的非叶页不会进行页压缩,但可进行行压缩。Non-leaf pages of an index are not page compressed but can be row compressed.
  • 每个非聚集索引都有单独的压缩设置,并且不会继承基础表的压缩设置。Each nonclustered index has an individual compression setting, and does not inherit the compression setting of the underlying table.
  • 对堆创建聚集索引时,聚集索引会继承该堆的压缩状态,除非指定了另一压缩状态。When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

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

  • 如果表具有非对齐索引,则无法更改单个分区的压缩设置。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.

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

权限Permissions

要求具有对表或视图的 ALTER 权限。Requires ALTER permission on the table or view. 用户必须是 sysadmin 固定服务器角色的成员,或者是 db_ddladmindb_owner 固定数据库角色的成员。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

限制和局限Limitations and Restrictions

SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 中不能执行以下创建:In SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse, you cannot create:

  • 当列存储索引已存在时,不能为数据仓库表创建聚集或非聚集行存储索引。A clustered or nonclustered rowstore index on a data warehouse table when a columnstore index already exists. 此行为与 SMP SQL ServerSQL Server 的行为不同,后者允许同一表中同时存在行存储和列存储索引。This behavior is different from SMP SQL ServerSQL Server which allows both rowstore and columnstore indexes to co-exist on the same table.
  • 不能对视图创建索引。You cannot create an index on a view.

元数据Metadata

若要查看现有索引的信息,可以查询 sys.indexes 目录视图。To view information on existing indexes, you can query the sys.indexes catalog view.

版本说明Version Notes

SQL 数据库SQL Database不支持文件组和文件流选项。does not support filegroup and filestream options.

示例:所有版本。Examples: All versions. 使用 AdventureWorks 数据库Uses the AdventureWorks database

A.A. 创建简单的非聚集行存储索引Create a simple nonclustered rowstore index

以下示例为 Purchasing.ProductVendor 表的 VendorID 列创建非聚集索引。The following examples create a nonclustered index on the VendorID column of the Purchasing.ProductVendor table.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B.B. 创建简单的非聚集行存储组合索引Create a simple nonclustered rowstore composite index

以下示例为 Sales.SalesPerson 表的 SalesQuotaSalesYTD 列创建非聚集组合索引。The following example creates a nonclustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C.C. 为其他数据库中的表创建索引Create an index on a table in another database

以下示例为 Purchasing 数据库中 ProductVendor 表的 VendorID 列创建聚集索引。The following example creates a clustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D.D. 将列添加到索引Add a column to an index

以下示例在 dbo.FactFinance 表中创建具有两列的 IX_FF 索引。The following example creates index IX_FF with two columns from the dbo.FactFinance table. 下一个语句重新生成具有三列的索引并保留现有名称。The next statement rebuilds the index with one more column and keeps the existing name.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

示例:SQL Server、Azure SQL 数据库Examples: SQL Server, Azure SQL Database

E.E. 创建唯一非聚集索引Create a unique nonclustered index

以下示例为 AdventureWorks2012AdventureWorks2012 数据库中 Name 表的 Production.UnitMeasure 列创建唯一非聚集索引。The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database. 该索引将强制插入 Name 列中的数据具有唯一性。The index will enforce uniqueness on the data inserted into the Name column.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

以下查询通过尝试插入与现有行包含相同值的一行来测试唯一性约束。The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

生成如下错误消息:The resulting error message is:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F.F. 使用 IGNORE_DUP_KEY 选项Use the IGNORE_DUP_KEY option

以下示例首先在该选项设置为 IGNORE_DUP_KEY 时在临时表中插入多行,然后在该选项设置为 ON 时执行相同操作,以演示 OFF 选项的影响。The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to ON and again with the option set to OFF. 单个行被插入 #Test 表,在执行第二个多行 INSERT 语句时将导致出现重复值。A single row is inserted into the #Test table that will intentionally cause a duplicate value when the second multiple-row INSERT statement is executed. 表中的行计数会返回插入的行数。A count of rows in the table returns the number of rows inserted.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。Here are the results of the second INSERT statement.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

请注意,从 Production.UnitMeasure 表中插入的、不违反唯一性约束的行将成功插入。Notice that the rows inserted from the Production.UnitMeasure table that did not violate the uniqueness constraint were successfully inserted. 会发出警告并忽略重复行,但不会回滚整个事务。A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.

将再次执行相同语句,但将 IGNORE_DUP_KEY 设置为 OFFThe same statements are executed again, but with IGNORE_DUP_KEY set to OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。Here are the results of the second INSERT statement.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

请注意,即使 Production.UnitMeasure 表中只有一行违反 UNIQUE 索引约束,也不会将其中任何一行插入该表。Notice that none of the rows from the Production.UnitMeasure table were inserted into the table even though only one row in the table violated the UNIQUE index constraint.

G.G. 使用 DROP_EXISTING 删除和重新创建索引Using DROP_EXISTING to drop and re-create an index

以下示例使用 ProductID 选项在 Production.WorkOrder 数据库的 AdventureWorks2012AdventureWorks2012 表的 DROP_EXISTING 列上删除并重新创建现有索引。The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table in the AdventureWorks2012AdventureWorks2012 database by using the DROP_EXISTING option. 还设置了 FILLFACTORPAD_INDEX 选项。The options FILLFACTOR and PAD_INDEX are also set.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H.H. 为视图创建索引Create an index on a view

以下示例将创建一个视图并为该视图创建索引。The following example creates a view and an index on that view. 包含两个使用该索引视图的查询。Two queries are included that use the indexed view.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I.I. 创建具有包含列(非键列)的索引Create an index with included (non-key) columns

以下示例创建具有一个键列 (PostalCode) 和四个非键列(AddressLine1AddressLine2CityStateProvinceID)的非聚集索引。The following example creates a nonclustered index with one key column (PostalCode) and four non-key columns (AddressLine1, AddressLine2, City, StateProvinceID). 然后执行该索引覆盖的查询。A query that is covered by the index follows. 若要显示查询优化器选择的索引,执行查询前,请在 SQL Server Management StudioSQL Server Management Studio 中的“查询”菜单上选择“显示实际执行计划” 。To display the index that is selected by the query optimizer, on the Query menu in SQL Server Management StudioSQL Server Management Studio, select Display Actual Execution Plan before executing the query.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J.J. 创建已分区索引Create a partitioned index

以下示例为 AdventureWorks2012AdventureWorks2012 数据库中现有分区方案 TransactionsPS1 创建非聚集分区索引。The following example creates a nonclustered partitioned index on TransactionsPS1, an existing partition scheme in the AdventureWorks2012AdventureWorks2012 database. 此示例假定安装了分区索引示例。This example assumes the partitioned index sample has been installed.

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K.K. 创建筛选索引Creating a filtered index

下面的示例将对 AdventureWorks2012AdventureWorks2012 数据库中的 Production.BillOfMaterials 表创建筛选索引。The following example creates a filtered index on the Production.BillOfMaterials table in the AdventureWorks2012AdventureWorks2012 database. 筛选谓词可包含那些不是筛选索引中的键列的列。The filter predicate can include columns that are not key columns in the filtered index. 本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。The predicate in this example selects only the rows where EndDate is non-NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L.L. 创建压缩索引Create a compressed index

下面的示例将使用行压缩对无分区表创建索引。The following example creates an index on a nonpartitioned table by using row compression.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

下面的示例将通过对索引的所有分区使用行压缩来创建对已分区表的索引。The following example creates an index on a partitioned table by using row compression on all partitions of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

下面的示例将通过对索引的分区 1 使用页压缩并对索引的分区 24 使用行压缩来创建对已分区表的索引。The following example creates an index on a partitioned table by using page compression on partition 1 of the index and row compression on partitions 2 through 4 of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M.M. 创建、恢复、暂停和中止可恢复索引操作Create, resume, pause, and abort resumable index operations

适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL Database(公共预览版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database (public preview)

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

N.N. 基本语法Basic syntax

创建、恢复、暂停和中止可恢复索引操作Create, resume, pause, and abort resumable index operations

适用对象SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL Database(公共预览版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database (public preview)

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

O.O. 为当前数据库中的表创建非聚集索引Create a nonclustered index on a table in the current database

以下示例为 VendorID 表的 ProductVendor 列创建非聚集索引。The following example creates a nonclustered index on the VendorID column of the ProductVendor table.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

P.P. 为其他数据库中的表创建聚集索引Create a clustered index on a table in another database

以下示例为 VendorID 数据库中 ProductVendor 表的 Purchasing 列创建非聚集索引。The following example creates a nonclustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

Q.Q. 在表上创建有序的聚集索引Create an ordered clustered index on a table

下面的示例在 MyDB 数据库的 T1 表上的 c1c2 列创建有序的聚集索引。The following example creates an ordered clustered index on the c1 and c2 columns of the T1 table in the MyDB database.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

R.R. 在表上将 CCI 转换为有序的聚集索引Convert a CCI to an ordered clustered index on a table

下面的示例将现有的聚集列存储索引转换为有序聚集列存储索引,该索引名为 MyOrderedCCI,位于 MyDB 数据库的 T2 表上的 c1c2 列。The following example convert the existing clustered columnstore index to an ordered clustered columnstore index called MyOrderedCCI on the c1 and c2 columns of the T2 table in the MyDB database.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

另请参阅See Also

SQL Server 索引体系结构和设计指南 SQL Server Index Architecture and Design Guide
联机执行索引操作Perform Index Operations Online
索引和 ALTER TABLE Indexes and ALTER TABLE
ALTER INDEX ALTER INDEX
CREATE PARTITION FUNCTION CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME CREATE PARTITION SCHEME
CREATE SPATIAL INDEX CREATE SPATIAL INDEX
CREATE STATISTICS CREATE STATISTICS
CREATE TABLE CREATE TABLE
CREATE XML INDEX CREATE XML INDEX
数据类型 Data Types
DBCC SHOW_STATISTICS DBCC SHOW_STATISTICS
DROP INDEX DROP INDEX
XML 索引 (SQL Server) XML Indexes (SQL Server)
sys.indexes sys.indexes
sys.index_columns sys.index_columns
sys.xml_indexes sys.xml_indexes
EVENTDATAEVENTDATA