销售电话: 1-800-867-1380
此文章由人工翻译。 将光标移到文章的句子上,以查看原文。 更多信息。
译文
原文
信息
您所需的主题如下所示。但此主题未包含在此库中。

CREATE INDEX (Transact-SQL)

 

对指定表或指定表的视图创建关系索引。可在向表中填入数据前创建索引。可通过指定限定的数据库名称对另一个数据库中的表或视图创建关系索引。

System_CAPS_note注意

在允许对表执行插入操作之前,版本早于 V12 的 Azure SQL Database 中的表必须具有一个聚集索引。

有关如何创建 XML 索引的信息,请参阅 CREATE XML INDEX (Transact-SQL)有关如何创建空间索引的信息,请参阅 CREATE SPATIAL INDEX (Transact-SQL)有关如何创建 xVelocity 内存优化的列存储索引的信息,请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL)

适用范围:SQL Server(SQL Server 2008 至当前版本)、Azure SQL Database、SQL 数据库 V12(获取它)。

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

-- SQL Server Syntax (All options except filegroup and filestream apply to SQL Database Update.)

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 ] . | schema_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 }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { 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 IndexImportant   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. 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 
}
-- Windows Azure SQL Database Syntax 

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

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

<relational_index_option> ::=
{
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
}

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

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

<disjunct> ::=column_name IN (constant ,…)

<comparison> ::=column_name <comparison_op> constant<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

UNIQUE

为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。

无论 IGNORE_DUP_KEY 是否设置为 ON,数据库引擎都不允许为已包含重复值的列创建唯一索引。否则,数据库引擎会显示错误消息。必须先删除重复值,然后才能为一列或多列创建唯一索引。唯一索引中使用的列应设置为 NOT NULL,因为在创建唯一索引时,会将多个 Null 值视为重复值。

CLUSTERED

创建索引时,键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层(或称叶级别)包含该表的实际数据行。一个表或视图只允许同时有一个聚集索引。

具有唯一聚集索引的视图称为索引视图。为一个视图创建唯一聚集索引会在物理上具体化该视图。必须先为视图创建唯一聚集索引,然后才能为该视图定义其他索引。有关详细信息,请参阅创建索引视图

在创建任何非聚集索引之前创建聚集索引。创建聚集索引时会重新生成表中现有的非聚集索引。

如果没有指定 CLUSTERED,则创建非聚集索引。

System_CAPS_note注意

因为按照定义,聚集索引的叶级别与其数据页相同,所以创建聚集索引和使用 ON partition_scheme_name 或 ON filegroup_name 子句实际上会将表从创建该表时所在的文件组移到新的分区方案或文件组中。对特定的文件组创建表或索引之前,应确认哪些文件组可用并且有足够的空间供索引使用。

在某些情况下,创建聚集索引可以启用以前禁用的索引。有关详细信息,请参阅启用索引和约束禁用索引和约束

NONCLUSTERED

创建一个指定表的逻辑排序的索引。对于非聚集索引,数据行的物理排序独立于索引排序。

无论是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建索引,还是使用 CREATE INDEX 显式创建索引,每个表都最多可包含 999 个非聚集索引。

对于索引视图,只能为已定义唯一聚集索引的视图创建非聚集索引。

默认值为 NONCLUSTERED。

index_name

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

column

索引所基于的一列或多列。指定两个或多个列名,可为指定列的组合值创建组合索引。table_or_view_name 后的括号中,按排序优先级列出组合索引中要包括的列。

一个组合索引键中最多可组合 16 列。组合索引键中的所有列必须在同一个表或视图中。组合索引值允许的最大大小为 900 字节。借助 SQL 数据库 V12,对聚集和非聚集索引中的用户定义列的数目限制将扩展到 32 列。非聚集索引的大小限制将扩展到 1700 字节。

不能将大型对象 (LOB) 数据类型 ntexttextvarchar(max) nvarchar(max)varbinary(max)xmlimage 的列指定为索引的键列。另外,即使 CREATE INDEX 语句中并未引用 ntexttextimage 列,视图定义中也不能包含这些列。

如果 CLR 用户定义类型支持二进制排序,则可以为该类型的列创建索引。另外,对于已定义为用户定义类型列的方法调用的计算列,只要这些方法标记为确定性方法且不执行数据访问操作,便可为该计算列创建索引。有关为 CLR 用户定义类型的列创建索引的详细信息,请参阅 CLR 用户定义类型

[ ASC | DESC ]

确定特定索引列的升序或降序排序方向。默认值为 ASC。

INCLUDE (column [ ,... n ] )

指定要添加到非聚集索引的叶级别的非键列。非聚集索引可以唯一,也可以不唯一。

在 INCLUDE 列表中列名不能重复,且不能同时用于键列和非键列。如果对表定义了聚集索引,则非聚集索引始终包含聚集索引列。有关详细信息,请参阅创建带有包含列的索引

textntextimage 之外,允许所有数据类型。如果指定的任一非键列属于 varchar(max)nvarchar(max)varbinary(max) 数据类型,则必须脱机 (ONLINE = OFF) 创建或重新生成该索引。

精确或不精确的确定性计算列都可以是包含列。imagentexttextvarchar(max)nvarchar(max)varbinary(max)xml 数据类型派生的计算列可以包含在非键列中,前提是允许将这些计算列数据类型作为包含列。有关详细信息,请参阅计算列上的索引

有关创建 XML 索引的信息,请参阅 CREATE XML INDEX (Transact-SQL)

WHERE <filter_predicate>

通过指定索引中要包含哪些行来创建筛选索引。筛选索引必须是对表的非聚集索引。为筛选索引中的数据行创建筛选统计信息。

筛选谓词使用简单比较逻辑且不能引用计算列、UDT 列、空间数据类型列或 hierarchyID 数据类型列。比较运算符不允许使用 NULL 文本的比较。请改用 IS NULL 和 IS NOT NULL 运算符。

下面是一些 Production.BillOfMaterials 表筛选谓词示例:

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

WHERE ComponentID IN (533, 324, 753)

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

筛选索引不适用于 XML 索引和全文检索。对于 UNIQUE 索引,仅选定的行必须具有唯一的索引值。筛选索引不允许有 IGNORE_DUP_KEY 选项。

ON partition_scheme_name(column_name)

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

指定分区方案,该方案定义要将分区索引的分区映射到的文件组。必须通过执行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME,使数据库中存在该分区方案。 column_name 指定对已分区索引进行分区所依据的列。该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。 column_name 不限于索引定义中的列。除了在对 UNIQUE 索引分区时,必须从用作唯一键的列中选择 column_name 外,还可以指定基表中的任何列。通过此限制,数据库引擎可验证单个分区中的键值唯一性。

System_CAPS_note注意

在对非唯一的聚集索引进行分区时,如果尚未指定分区依据列,则默认情况下数据库引擎将在聚集索引键列表中添加分区依据列。在对非唯一的非聚集索引进行分区时,如果尚未指定分区依据列,则数据库引擎会添加分区依据列作为索引的非键(包含)列。

如果未指定 partition_scheme_namefilegroup 且该表已分区,则索引会与基础表使用相同分区依据列并被放入同一分区方案中。

System_CAPS_note注意

您不能对 XML 索引指定分区方案。如果基表已分区,则 XML 索引与该表使用相同的分区方案。

有关将索引分区的详细信息,请参阅已分区表和已分区索引

ON filegroup_name

适用范围:SQL Server 2008 到 SQL Server 2014。

为指定文件组创建指定索引。如果未指定位置且表或视图尚未分区,则索引将与基础表或视图使用相同的文件组。该文件组必须已存在。

ON "default"

为默认文件组创建指定索引。

在此上下文中,“default”一词不是关键字。它是默认文件组的标识符,并且必须进行分隔(类似于 ON "default" 或 ON [default])。如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。这是默认设置。有关详细信息,请参阅SET QUOTED_IDENTIFIER (Transact-SQL)

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

适用范围:SQL Server 2008 到 SQL Server 2014。

在创建聚集索引时,指定表的 FILESTREAM 数据的位置。FILESTREAM_ON 子句用于将 FILESTREAM 数据移动到不同的 FILESTREAM 文件组或分区方案。

filestream_filegroup_name 是 FILESTREAM 文件组的名称。该文件组必须包含一个使用 CREATE DATABASEALTER DATABASE 语句为该文件组定义的文件;否则,将引发错误。

如果表已分区,则必须包含 FILESTREAM_ON 子句并且必须指定 FILESTREAM 文件组的分区方案,且此分区方案需使用与该表分区方案相同的分区函数和分区列。否则将引发错误。

如果该表未分区,则无法对 FILESTREAM 列分区。该表的 FILESTREAM 数据必须存储在一个由 FILESTREAM_ON 子句指定的文件组中。

如果创建的是聚集索引且该表不包含 FILESTREAM 列,则可在 CREATE INDEX 语句中指定 FILESTREAM_ON NULL。

有关详细信息,请参阅FILESTREAM (SQL Server)

<object>::=

要为其建立索引的完全限定对象或非完全限定对象。

database_name

数据库的名称。

schema_name

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

table_or_view_name

要为其建立索引的表或视图的名称。

必须使用 SCHEMABINDING 定义视图,才能为视图创建索引。必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。有关索引视图的详细信息,请参阅“备注”部分。

Azure SQL Database 支持由三部分组成的名称格式 database_name.[schema_name].object_name,其中 database_name 是当前数据库,database_name 是 tempdb,object_name 以 # 开头。

<relational_index_option>::=

指定创建索引时要使用的选项。

PAD_INDEX = { ON | OFF }

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

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

ON

fillfactor 指定的可用空间百分比应用于索引的中间级页。

OFF 或不指定 fillfactor

考虑到中间级页上的键集,将中间级页填充到接近其容量的程度,以留出足够的空间,使之至少能够容纳索引的最大的一行。

PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。如果为 FILLFACTOR 指定的百分比不够大,无法容纳一行,数据库引擎将在内部覆盖该百分比以允许最小值。中间级索引页上的行数永远都不会小于两行,无论 fillfactor 的值有多小。

在向后兼容的语法中,WITH PAD_INDEX 等效于 WITH PAD_INDEX = ON。

FILLFACTOR =fillfactor

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

指定一个百分比,指示在数据库引擎创建或重新生成索引的过程中,应将每个索引页面的叶级填充到什么程度。 fillfactor 必须为介于 1 至 100 之间的整数值。如果 fillfactor 为 100,则数据库引擎将创建完全填充叶级页的索引。

FILLFACTOR 设置仅在创建或重新生成索引时应用。数据库引擎并不会在页中动态保持指定的可用空间百分比。若要查看填充因子设置,请使用 sys.indexes 目录视图。

System_CAPS_important重要事项

使用低于 100 的 FILLFACTOR 值创建聚集索引会影响数据占用的存储空间量,因为数据库引擎在创建聚集索引时会重新分布数据。

有关详细信息,请参阅为索引指定填充因子

SORT_IN_TEMPDB = { ON | OFF }

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

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

ON

tempdb 中存储用于生成索引的中间排序结果。如果 tempdb 与用户数据库不在同一组磁盘上,就可缩短创建索引所需的时间。但是,这会增加索引生成期间所使用的磁盘空间量。

OFF

中间排序结果与索引存储在同一数据库中。

除在用户数据库中创建索引所需的空间外,tempdb 还必须有大约相同的额外空间来存储中间排序结果。有关详细信息,请参阅用于索引的 SORT_IN_TEMPDB 选项

在向后兼容的语法中,WITH SORT_IN_TEMPDB 等效于 WITH SORT_IN_TEMPDB = ON。

IGNORE_DUP_KEY = { ON | OFF }

指定在插入操作尝试向唯一索引插入重复键值时的错误响应。IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。当执行 、ALTER INDEXUPDATE 时,该选项无效。默认为 OFF。

ON

向唯一索引插入重复键值时将出现警告消息。只有违反唯一性约束的行才会失败。

OFF

向唯一索引插入重复键值时将出现错误消息。整个 INSERT 操作将被回滚。

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

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

在向后兼容的语法中,WITH IGNORE_DUP_KEY 等效于 WITH IGNORE_DUP_KEY = ON。

STATISTICS_NORECOMPUTE = { ON | OFF}

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

ON

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

OFF

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

若要恢复统计信息自动更新,请将 STATISTICS_NORECOMPUTE 设置为 OFF,或执行 UPDATE STATISTICS 但不包含 NORECOMPUTE 子句。

System_CAPS_important重要事项

如果禁用分布统计的自动重新计算,可能会妨碍查询优化器为涉及该表的查询选取最佳执行计划。

在向后兼容的语法中,WITH STATISTICS_NORECOMPUTE 等效于 WITH STATISTICS_NORECOMPUTE = ON。

STATISTICS_INCREMENTAL = { ON | OFF }

ON 时,根据分区统计信息创建统计信息。OFF 时,删除统计信息树并且 SQL Server 重新计算统计信息。默认值为 OFF

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

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

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

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

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

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

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

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

DROP_EXISTING = { ON | OFF }

指定应删除并重新生成已命名的先前存在的聚集或非聚集索引。默认为 OFF。

ON

删除并重新生成现有索引。指定的索引名称必须与当前的现有索引相同;但可以修改索引定义。例如,可以指定不同的列、排序顺序、分区方案或索引选项。

OFF

如果指定的索引名称已存在,则会显示一条错误。

使用 DROP_EXISTING 不能更改索引类型。

在向后兼容的语法中,WITH DROP_EXISTING 等效于 WITH DROP_EXISTING = ON。

ONLINE = { ON | OFF }

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

System_CAPS_note注意

并非在 MicrosoftSQL Server 的每个版本中均提供联机索引操作。有关 SQL Server 各版本支持的功能列表,请参阅 SQL Server 2014 各个版本支持的功能

ON

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

OFF

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

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

可以联机创建包括全局临时表上的索引在内的索引,但下列索引例外:

  • XML 索引

  • 对局部临时表的索引。

  • 视图唯一的初始聚集索引。

  • 已禁用的聚集索引。

  • 聚集索引,前提是基础表包含 LOB 数据类型:imagentexttext 和空间类型。

  • varchar(max)varbinary(max) 列不能是索引的一部分。在 SQL Server(从 SQL Server 2012 开始)和 SQL 数据库 V12 中,当表包含 varchar(max)varbinary(max) 列时,可以使用 ONLINE 选项来生成或重新生成包含其他列的聚集索引。当基表包含 varchar(max)SQL Database 或 varbinary(max) 列时, 不允许使用 ONLINE 选项。

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

ALLOW_ROW_LOCKS = { ON | OFF }

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

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

ON

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

OFF

不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

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

ON

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

OFF

不使用页锁。

MAXDOP = max_degree_of_parallelism

应用范围:SQL Server 2008 到 SQL Server 2014、SQL 数据库 V12(仅性能级别 P2 和 P3)。

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

max_degree_of_parallelism 可以是:

1

取消生成并行计划。

>1

基于当前系统工作负荷,将并行索引操作中使用的最大处理器数限制为指定数量或更少。

0(默认值)

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

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

System_CAPS_note注意

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

DATA_COMPRESSION

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

NONE

不压缩索引或指定的分区。

ROW

使用行压缩来压缩索引或指定的分区。

PAGE

使用页压缩来压缩索引或指定的分区。

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

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

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

指定对其应用 DATA_COMPRESSION 设置的分区。如果索引未分区,则 ON PARTITIONS 参数将产生错误。如果不提供 ON PARTITIONS 子句,则 DATA_COMPRESSION 选项将应用于分区索引的所有分区。

可以按以下方式指定 <partition_number_expression>:

  • 提供分区号,例如:ON PARTITIONS (2)。

  • 为多个单个分区提供分区号,用逗号分隔,例如:ON PARTITIONS (1,5)。

  • 同时提供范围和单个分区,例如:ON PARTITIONS (2, 4, 6 TO 8)。

<范围> 可指定为用单词“TO”分开的分区号,例如:ON PARTITIONS (6 TO 8)。

若要为不同分区设置不同的数据压缩类型,请多次指定 DATA_COMPRESSION 选项,例如:

REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

CREATE INDEX 语句同其他查询一样优化。为了节省 I/O 操作,查询处理器可以选择扫描另一个索引,而不是执行表扫描。在某些情况下,可不必执行排序操作。在多处理器计算机上,CREATE INDEX 可按照与其他查询相同的方式,使用多个处理器执行与创建索引相关的扫描和排序操作。有关详细信息,请参阅配置并行索引操作

如果数据库恢复模型被设置为大容量日志模型或简单模型,则可以记录最少的创建索引操作。

可以为临时表创建索引。在删除表或结束会话时,将删除索引。

索引支持扩展属性。

对表(堆)创建聚集索引或删除和重新创建现有聚集索引时,要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。有关聚集索引的详细信息,请参阅创建聚集索引

如果存在唯一索引,数据库引擎会在每次插入操作添加数据时检查重复值。可生成重复键值的插入操作将被回滚,同时数据库引擎显示错误消息。即使插入操作更改多行但只导致出现一个重复值时,也是如此。如果在存在唯一索引并且 IGNORE_DUP_KEY 子句设置为 ON 的情况下输入数据,则只有违反 UNIQUE 索引的行才会失败。

创建和维护分区索引的方式与已分区表相同,但与普通索引一样,将分区索引作为单独数据库对象来进行处理。可以在未分区的表中使用分区索引,也可以在已分区表中使用未分区索引。

如果要对已分区表创建索引,并且不指定用于放置该索引的文件组,则会按照与基础表相同的方式为该索引分区。这是因为在默认情况下,索引与其基础表放在同一文件组中,并且对应使用相同分区依据列的相同分区方案中的已分区表。当索引与表使用同一个分区方案和分区列时,索引将与表对齐。

System_CAPS_warning警告

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

在对非唯一的聚集索引分区时,如果尚未指定分区依据列,则默认情况下数据库引擎将在聚集索引键列表中添加任意分区依据列。

可以使用与为表创建索引时相同的方式,为已分区表创建索引视图。有关已分区索引的详细信息,请参阅已分区表和已分区索引

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

筛选索引是一种经过优化的非聚集索引,适用于从表中选择少数行的查询。筛选索引使用筛选谓词对表中的部分数据进行索引。设计良好的筛选索引可以提高查询性能,降低存储成本和维护成本。

如果下列任何条件成立,则需要 Required Value 列中的 SET 选项:

  • 创建筛选索引。

  • INSERT、UPDATE、DELETE 或 MERGE 操作修改筛选索引中的数据。

  • 查询优化器使用查询执行计划中的筛选索引。

    SET 选项

    必需的值

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

    *当数据库兼容性级别设置为 90 或更高时,如果将 ANSI_WARNINGS 设置为 ON,则将使 ARITHABORT 隐式设置为 ON。如果数据库兼容性级别设置为 80 或更低,则必须将 ARITHABORT 选项显式设置为 ON。

如果 SET 选项不正确,则可能会出现以下情况:

  • 不会创建筛选索引。

  • 数据库引擎生成错误并回滚对索引中的数据进行更改的 INSERT、UPDATE、DELETE 或 MERGE 语句。

  • 查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。

有关筛选的索引的详细信息,请参阅创建筛选索引

有关空间索引的信息,请参阅 CREATE SPATIAL INDEX (Transact-SQL)空间索引概述

有关 XML 索引的详细信息,请参阅 CREATE XML INDEX (Transact-SQL)XML 索引 (SQL Server)

对于 SQL Server,索引键的最大大小为 900 字节。对于 SQL 数据库 V12,非聚集索引的大小限制将(从 900 字节)扩展到 1700 字节。如果创建索引时,varchar 列中的现有数据未超过限制,则可以对这些列创建超过字节限制的索引;但是,以后在这些列上执行会导致总大小超过该限制的插入或更新操作时将失败。聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果对 varchar 列创建了聚集索引,并且在 IN_ROW_DATA 分配单元中存在现有数据,则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。

非聚集索引可以在索引的叶级别包含非键列。计算索引键大小时,数据库引擎不考虑这些列。有关详细信息,请参阅创建带有包含列的索引

System_CAPS_note注意

在对表进行分区时,如果分区键列尚未出现在非唯一聚集索引中时,它们将由数据库引擎添加到索引中。索引列的合并后的大小(不将包含列计算在内)加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节。

可以对计算列创建索引。此外,计算列可以具有 PERSISTED 属性。这意味着数据库引擎在表中存储计算值,并且在计算列所依赖的任何其他列发生更新时更新这些值。如果数据库引擎对列创建了索引并且该索引由某查询引用,则会使用这些持久值。

若要对计算列建立索引则该计算列必须具有确定性并精确。但是,使用 PERSISTED 属性会将可建立索引的计算列类型扩展为包含以下类型:

  • 基于 Transact-SQL 和 CLR 函数以及由用户标记为确定性的 CLR 用户定义类型方法的计算列。

  • 基于数据库引擎定义为确定性但不精确的表达式的计算列。

持久化计算列需要将以下 SET 选项设置为在上一部分“索引视图所需的 SET 选项”中显示的内容。

UNIQUE 或 PRIMARY KEY 约束只要满足所有索引条件,就可以包含计算列。具体来说,计算列必须具有确定性并精确,或者具有确定性并持久化。有关确定性的详细信息,请参阅确定性函数和不确定性函数

可以对从 imagentexttextvarchar(max)nvarchar(max)varbinary(max)xml 数据类型派生的计算列建立索引以作为键列或包含性非键列,条件是允许将该计算列数据类型作为索引键列或非键列。例如,不能对 xml 计算列创建主 XML 索引。如果索引键大小超过 900 字节,会显示一条警告消息。

对计算列创建索引可能导致之前正常运行的插入或更新操作失败。当计算列导致算术错误时可能产生这样的失败。例如,虽然下表中的计算列 c 将导致算术错误,但是 INSERT 语句仍有效。

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

相反,如果创建表之后对计算列 c 创建索引,则上述 INSERT 语句将失败。

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

有关详细信息,请参阅计算列上的索引

可以将非键列(称为包含列)添加到非聚集索引的叶级别,从而通过涵盖查询来提高查询性能。也就是说,查询中引用的所有列都作为键列或非键列包含在索引中。这样,查询优化器可以通过索引扫描找到所需的全部信息,而无需访问表或聚集索引数据。有关详细信息,请参阅创建带有包含列的索引

SQL Server 2005 中引入了新的索引选项,还修改了指定选项的方式。在向后兼容的语法中,WITH option_name 等效于 WITH ( <option_name> = ON )在设置索引选项时,下列规则适用:

  • 只能使用 WITH (option_name= ON | OFF) 指定新的索引选项。

  • 指定选项时不能在同一语句中同时使用向后兼容语法和新语法。例如,指定 WITH (DROP_EXISTING, ONLINE = ON) 会导致语句失败。

  • 在创建 XML 索引时,必须使用 WITH (option_name= ON | OFF) 指定选项。

可使用 DROP_EXISTING 子句重新生成索引、添加或删除列、修改选项、修改列排序顺序或更改分区方案或文件组。

如果索引强制 PRIMARY KEY 或 UNIQUE 约束,且索引定义没有任何改变,则会删除并重新创建该索引并保留现有约束。不过,如果索引定义已改变,则该语句将失败。若要更改 PRIMARY KEY 或 UNIQUE 约束的定义,请删除该约束并添加具有新定义的约束。

为已经具有非聚集索引的表重建聚集索引时(使用相同或不同的键集),DROP_EXISTING 可以提高性能。DROP_EXISTING 代替先对旧的聚集索引执行 DROP INDEX 语句,然后再对新的聚集索引执行 CREATE INDEX 语句的过程。而是将重新生成一次非聚集索引,之后仅在索引定义已更改时再重新生成。如果索引定义与原始索引具有相同的索引名称、键列和分区列、唯一性属性以及排序顺序,则 DROP_EXISTING 子句不会重新生成非聚集索引。

无论是否重新生成非聚集索引,它们都将始终保留在其原始文件组或分区方案中,并使用原始的分区函数。如果聚集索引被重新生成到其他文件组或分区方案中,这些非聚集索引不会通过移动来与聚集索引的新位置保持一致。所以,即使非聚集索引以前与聚集索引对齐,现在可能也不再与其对齐。有关对齐已分区索引的详细信息,请参阅有关内容。

如果以相同顺序使用相同索引键列,且具有相同升序和降序,则 DROP_EXISTING 子句不会重新对数据排序,除非索引语句指定非聚集索引且 ONLINE 选项设置为 OFF。如果聚集索引被禁用,则必须在 ONLINE 设置为 OFF 的情况下执行 CREATE INDEX WITH DROP_EXISTING 操作。如果非聚集索引被禁用且不与禁用的聚集索引关联,则可以在 ONLINE 设置为 OFF 或 ON 时执行 CREATE INDEX WITH DROP_EXISTING 操作。

删除或重新生成具有 128 个或更多区数的索引时,数据库引擎会将实际页释放及其关联的锁推迟到事务提交后。

下列指南适用于联机执行索引操作:

  • 不能在执行联机索引操作的过程中更改、截断或删除基础表。

  • 索引操作期间需要额外的临时磁盘空间。

  • 可以对分区索引以及包含持久性计算列或包含列的索引执行联机操作。

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

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

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF,则访问索引时仅允许使用表级锁。

若要返回有关索引的信息,可以使用目录视图、系统函数和系统存储过程。

数据压缩会在数据压缩主题中进行介绍。以下是要考虑的关键点:

  • 通过压缩可将更多的行存储在页上,但不能更改最大行大小。

  • 对索引的非叶页不会进行页压缩,但可进行行压缩。

  • 每个非聚集索引都有单独的压缩设置,并且不会继承基础表的压缩设置。

  • 对堆创建聚集索引时,聚集索引会继承该堆的压缩状态,除非指定了另一压缩状态。

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

  • 如果表具有非对齐索引,则无法更改单个分区的压缩设置。

  • ALTER INDEX <index> ...REBUILD PARTITION ... 语法可重新生成索引的指定分区。

  • ALTER INDEX <index> ...REBUILD WITH ... 语法可重新生成索引的所有分区。

若要评估更改压缩状态将对表、索引或分区有何影响,请使用 sp_estimate_data_compression_savings 存储过程。

要求对表或视图具有 ALTER 权限。用户必须是 sysadmin 固定服务器角色的成员,或者是 db_ddladmindb_owner 固定数据库角色的成员。

以下示例为 VendorID 数据库中 Purchasing.ProductVendor 表的 AdventureWorks2012 列创建非聚集索引。

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID);

以下示例为 SalesQuota 数据库中 SalesYTD 表的 Sales.SalesPerson 和 AdventureWorks2012列创建非聚集组合索引。

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

以下示例为 Name 数据库中 Production.UnitMeasure 表的 AdventureWorks2012 列创建唯一非聚集索引。该索引将强制插入 Name 列中的数据具有唯一性。

IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);

以下查询通过尝试插入与现有行包含相同值的一行来测试唯一性约束。

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

生成如下错误消息:

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.

以下示例首先在该选项设置为 IGNORE_DUP_KEY 时在临时表中插入多行,然后在该选项设置为 ON 时执行相同操作,以演示 OFF 选项的影响。单个行被插入 #Test 表,在执行第二个多行 INSERT 语句时将导致出现重复值。表中的行计数会返回插入的行数。

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 语句的结果。

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

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

请注意,从 Production.UnitMeasure 表中插入的、不违反唯一性约束的行将成功插入。会发出警告并忽略重复行,但不会回滚整个事务。

将再次执行相同语句,但将 IGNORE_DUP_KEY 设置为 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 语句的结果。

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 索引约束,也不会将其中任何一行插入该表。

以下示例使用 ProductID 选项在 Production.WorkOrder 数据库的 AdventureWorks2012 表的 DROP_EXISTING 列上删除并重新创建现有索引。还设置了 FILLFACTORPAD_INDEX 选项。

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

以下示例将创建一个视图并为该视图创建索引。包含两个使用该索引视图的查询。

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

以下示例创建具有一个键列 (PostalCode) 和四个非键列(AddressLine1AddressLine2CityStateProvinceID)的非聚集索引。然后执行该索引覆盖的查询。若要显示查询优化器选择的索引,执行查询前请在 中的“查询”SQL Server Management Studio菜单上选择“显示实际执行计划”

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
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


以下示例为 TransactionsPS1 数据库中现有分区方案 AdventureWorks2012 创建非聚集分区索引。此示例假定安装了分区索引示例。

适用范围:SQL Server 2008 到 SQL Server 2014,SQL 数据库 V12。

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

下面的示例将对 AdventureWorks2012 数据库中的 Production.BillOfMaterials 表创建筛选索引。筛选谓词可包含那些不是筛选索引中的键列的列。本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

下面的示例将使用行压缩对无分区表创建索引。

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

下面的示例将通过对索引的所有分区使用行压缩来创建对已分区表的索引。

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

下面的示例将通过对索引的分区 1 使用页压缩并对索引的分区 24 使用行压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO
本文是否对您有所帮助?
(1500 个剩余字符)
感谢您的反馈
显示:
© 2016 Microsoft