索引的创建及使用(sqlserver 2005)

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

 Transact-SQL 语法约定

语法
 
Create Relational Index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
[ ; ]

<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 }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

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

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   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
}
 

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

如果数据库恢复模型被设置为大容量日志模型或简单模型，则可以记录最少的创建索引操作。有关详细信息，请参阅为索引操作选择恢复模式。

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

索引支持扩展属性。有关详细信息，请参阅对数据库对象使用扩展属性。

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

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

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

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

在为非唯一的聚集索引分区时，如果未指定，则默认情况下，数据库引擎 会将所有分区依据列都添加到聚集索引键的列表中。

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

索引视图
为视图创建唯一聚集索引可以提高查询性能，因为视图在数据库中的存储方式与具有聚集索引的表的存储方式相同。查询优化器可使用索引视图加快执行查询的速度。要使优化器考虑将该视图作为替换，并不需要在查询中引用该视图。

创建索引视图所需的下列步骤对于成功实现视图而言非常重要：

验证是否视图中将引用的所有现有表的 SET 选项都正确。

在创建任何新表和视图之前，验证会话的 SET 选项设置是否正确。

验证视图定义是否为确定性的。

使用 WITH SCHEMABINDING 选项创建视图。

为视图创建唯一的聚集索引。

索引视图所需的 SET 选项
如果执行查询时启用不同的 SET 选项，则在数据库引擎 中对同一表达式求值会产生不同结果。 例如，将 SET 选项 CONCAT_NULL_YIELDS_NULL 设置为 ON 后，表达式 'abc' + NULL 会返回值 NULL。但将 CONCAT_NULL_YIEDS_NULL 设置为 OFF 后，同一表达式会生成 'abc'。

为了确保能够正确维护视图并返回一致结果，索引视图需要多个 SET 选项具有固定值。如果下列条件成立，则下表中的 SET 选项必须设置为“必需的值”列中显示的值：

索引视图已创建。

对构成该索引视图的任何表执行了任何插入、更新或删除操作。包括大容量复制、复制和分发查询等操作。

查询优化器使用该索引视图生成查询计划。

SET 选项  必需的值  默认服务器值  默认 OLE DB 和 ODBC 值  默认 DB-Library 值 
ANSI_NULLS
 ON
 ON
 ON
 OFF
 
ANSI_PADDING
 ON
 ON
 ON
 OFF
 
ANSI_WARNINGS
 ON
 ON
 ON
 OFF
 
ARITHABORT
 ON
 ON
 OFF
 OFF
 
CONCAT_NULL_YIELDS_NULL
 ON
 ON
 ON
 OFF
 
NUMERIC_ROUNDABORT
 OFF
 OFF
 OFF
 OFF
 
QUOTED_IDENTIFIER
 ON
 ON
 ON
 OFF
 

如果使用的是 OLE DB 或 ODBC 服务器连接，则唯一必须要修改的值是 ARITHABORT 设置。必须使用 sp_configure 在服务器级别或使用 SET 命令从应用程序中正确设置所有 DB-Library 值。有关 SET 选项的详细信息，请参阅使用 SQL Server 中的选项。

重要事项： 
强烈建议在服务器的任一数据库中创建计算列上的第一个索引视图或索引后，尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。
 

确定性函数
索引视图的定义必须是确定性的。如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性，则视图也具有确定性。在使用特定的输入值集对确定性表达式求值时，它们始终返回相同的结果。只有确定性函数可以加入确定性表达式。例如，DATEADD 函数是确定性函数，因为对于其三个参数的任何给定参数值集它总是返回相同的结果。GETDATE 不是确定性函数，因为总是使用相同的参数调用它，而它在每次执行时返回结果都不同。有关详细信息，请参阅确定性函数和不确定性函数。

即使是确定性表达式，如果其中包含浮点表达式，则准确结果也会取决于处理器体系结构或微代码的版本。为了确保数据完整性，此类表达式只能作为索引视图的非键列。不包含浮点表达式的确定性表达式称为精确表达式。只有精确的确定性表达式才能加入键列，并包含在索引视图的 WHERE 或 GROUP BY 子句中。

使用 COLUMNPROPERTY 函数的 IsDeterministic 属性确定视图列是否为确定性列。使用 COLUMNPROPERTY 函数的 IsPrecise 属性确定具有架构绑定的视图中的确定性列是否为精确列。如果为 TRUE，则 COLUMNPROPERTY 返回 1；如果为 FALSE，则返回 0；如果输入无效，则返回 NULL。这意味着该列不是确定性列，也不是精确列。

其他要求
除对 SET 选项和确定性函数的要求外，还必须满足下列要求：

执行 CREATE INDEX 的用户必须是视图所有者。

如果视图定义包含 GROUP BY 子句，则唯一聚集索引的键只能引用 GROUP BY 子句中指定的列。

在创建表时，基表必须具有正确的 SET 选项集，否则具有架构绑定的视图无法引用该表。

在视图定义中，必须使用两部分名称（即 schema.tablename）来引用表。

必须使用 WITH SCHEMABINDING 选项创建用户定义函数。

必须使用两部分名称 schema.function 来引用用户定义函数。

必须使用 WITH SCHEMABINDING 选项创建视图。

视图必须仅引用同一数据库中的基表，而不引用其他视图中的基表。

视图定义必须包含以下各部分：

COUNT(*)
 ROWSET 函数
 
派生表
 自联接
 
DISTINCT
 STDEV、VARIANCE、AVG
 
float*、text、ntext 或 image 列
 子查询
 
全文谓词（CONTAIN、FREETEXT）
 可为空表达式的 SUM
 
CLR 用户定义聚合函数
 TOP
 
MIN、MAX
 UNION
 
*索引视图可以包含 float 列；但聚集索引健中不能包含这类列。

如果存在 GROUP BY，则 VIEW 定义必须包含 COUNT_BIG(*)，并且不得包含 HAVING。这些 GROUP BY 限制仅适用于索引视图定义。即使一个索引视图不满足这些 GROUP BY 限制，查询也可以在其执行计划中使用该视图。

可以对已分区表创建索引视图，并可以由其自行分区。有关分区的详细信息，请参阅前一部分“已分区索引”。

若要防止数据库引擎 使用索引视图，请在查询中包含 OPTION (EXPAND VIEWS) 提示。此外，任何所列选项设置不正确均会阻止优化器使用视图上的索引。有关 OPTION (EXPAND VIEWS) 提示的详细信息，请参阅 SELECT (Transact-SQL)。

数据库的兼容级别不能低于 80。不能将包含索引视图的数据库的兼容级别更改为低于 80。

XML 索引
有关详细信息，请参阅xml 数据类型列的索引。

索引键大小
索引键的最大大小为 900 字节。如果创建索引时，varchar 列中的现有数据未超过 900 字节，则可以对这些列创建超过 900 字节的索引；但是，以后在这些列上执行会导致总大小超过 900 字节的插入或更新操作时将失败。有关详细信息，请参阅索引键的最大大小。聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果对 varchar 列创建了聚集索引，并且在 IN_ROW_DATA 分配单元中存在现有数据，则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。有关分配单元的详细信息，请参阅表组织和索引组织。

在 SQL Server 2005 中，非聚集索引可以在索引的叶级别包含非键列。计算索引键大小时，数据库引擎 不考虑这些列。有关详细信息，请参阅具有包含性列的索引。

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

使用此属性将可建立索引的计算列类型扩展为包含以下类型：

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

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

持久性计算列需要对下列 SET 选项进行如下设置：

ANSI_NULLS ON
 CONCAT_NULL_YIELDS_NULL ON
 
ANSI_PADDING ON
 NUMERIC_ROUNDABORT OFF
 
ANSI_WARNINGS ON
 QUOTED_IDENTIFIER ON
 
ARITHABORT ON
 
 
重要事项： 
强烈建议在服务器的任一数据库中创建计算列上的第一个索引后，尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。
 

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

从 image、ntext、text、varchar(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 子句
可使用 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 个或更多区数的索引时，数据库引擎 会将实际页释放及其关联的锁推迟到事务提交后。有关详细信息，请参阅删除并重新生成大型对象。

ONLINE 选项
下列指南适用于联机执行索引操作：

不能在 ONLINE 选项设置为 ON 时创建、重新生成或删除禁用的聚集索引。

不能在 ONLINE 选项设置为 ON 时创建、重新生成或删除 XML 索引。

不能在 ONLINE 选项设置为 ON 时创建视图上的唯一初始聚集索引。支持在索引视图上执行所有其他联机索引操作。例如，可以在 ONLINE 选项设置为 ON 时对视图重新生成索引或对视图创建非聚集索引。

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

索引操作期间需要额外的临时磁盘空间。有关详细信息，请参阅确定索引的磁盘空间要求。

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

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

行锁和页锁选项
如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，则访问索引时允许行级、页级和表级锁。数据库引擎 将选择相应的锁，并且可以将锁从行锁或页锁升级到表锁。有关详细信息，请参阅锁升级（数据库引擎）。

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

有关为索引配置锁定粒度的详细信息，请参阅自定义索引的锁定。

查看索引信息
若要返回有关索引的信息，可以使用目录视图、系统函数和系统存储过程。有关详细信息，请参阅查看索引信息。

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

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

CLUSTERED
创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层（或称叶级别）包含该表的实际数据行。一个表或视图只允许同时有一个聚集索引。有关详细信息，请参阅聚集索引结构。

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

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

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

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

NONCLUSTERED
创建一个指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。有关详细信息，请参阅非聚集索引结构。

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

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

默认值为 NONCLUSTERED。

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

主 XML 索引名不得以下列字符开头：#、##、@ 或 @@。

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

一个组合索引键中最多可组合 16 列。组合索引键中的所有列必须在同一个表或视图中。组合索引值允许的最大大小为 900 字节。有关组合索引中的可变类型列的详细信息，请参阅“备注”部分。

不能将大型对象 (LOB) 数据类型 ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml 或 image 的列指定为索引的键列。另外，即使 CREATE INDEX 语句中并未引用 ntext、text 或 image 列，视图定义中也不能包含这些列。

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

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

INCLUDE ( column [ ,... n ] )
指定要添加到非聚集索引的叶级别的非键列。非聚集索引可以唯一，也可以不唯一。

可包含的非键列的最大数量为 1,023 列；最小数量为 1 列。

INCLUDE 列表中列名不能重复，且列不能同时用作键列和非键列。有关详细信息，请参阅具有包含性列的索引。

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

精确或不精确的确定性计算列都可以是包含性列。从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列可以作为包含性非键列，前提是允许将这些计算列数据类型作为包含性列。有关详细信息，请参阅为计算列创建索引。

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

注意： 
在对非唯一的聚集索引分区时，如果未指定，则默认情况下数据库引擎 会将分区依据列添加到聚集索引键的列表中。在对非唯一的非聚集索引分区时，如果未指定，则数据库引擎 会将分区依据列添加为索引的非键（包含性）列。
 

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

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

有关将索引分区的详细信息，请参阅已分区索引的特殊指导原则。

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

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

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

[PRIMARY] XML
为指定 xml 列创建 XML 索引。指定 PRIMARY 时，会使用由用户表的聚集键形成的聚集键和 XML 节点标识符来创建聚集索引。每个表最多可具有 249 个 XML 索引。创建 XML 索引时请注意以下几点：

聚集索引必须存在于用户表的主键上。

用户表的聚集键被限制为 15 列。

表中的每个 xml 列可具有一个主 XML 索引和多个辅助 XML 索引。

xml 列中必须存在主 XML 索引，然后才能对该列创建辅助 XML 索引。

只能对单个 xml 列创建 XML 索引。不能为非 xml 列创建 XML 索引，也不能为 xml 列创建关系索引。

您不能对视图中的 xml 列、包含 xml 列的表值变量或 xml 类型变量创建主 XML 索引或辅助 XML 索引。

不能对 xml 计算列创建主 XML 索引。

SET 选项的设置必须与索引视图或计算列索引所需要的设置相同。具体来说，在创建 XML 索引以及在 xml 列中插入、删除或更新值时，选项 ARITHABORT 必须设置为 ON。有关详细信息，请参阅影响结果的 SET 选项。

有关详细信息，请参阅xml 数据类型列的索引。

xml_column_name
索引所基于的 xml 列。在一个 XML 索引定义中只能指定一个 xml 列；但可以为一个 xml 列创建多个辅助 XML 索引。

USING XML INDEX xml_index_name
指定创建辅助 XML 索引时要使用的主 XML 索引。

FOR { VALUE | PATH | PROPERTY }
指定辅助 XML 索引的类型。

VALUE
为主 XML 索引的键列（节点值和路径）所在的列创建辅助 XML 索引。

PATH
为基于主 XML 索引中的路径值和节点值生成的列创建辅助 XML 索引。在 PATH 辅助索引中，路径值和节点值是用于提高路径搜索效率的键列。

PROPERTY
为 PK 为基表主键的主 XML 索引列（PK、路径值和节点值）创建辅助 XML 索引。

<object>::=

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

database_name
数据库的名称。

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

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

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

<relational_index_option>::=

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

PAD_INDEX = { ON | OFF }
指定索引填充。默认值为 OFF。

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

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

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

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

FILLFACTOR = fillfactor
指定一个百分比，指示在创建或重新生成索引的过程中数据库引擎 使每个索引页的叶级别的填充程度。fillfactor 必须为 1 至 100 之间的整数值。默认值为 0。如果 fillfactor 为 100 或 0，则数据库引擎 会创建叶级页的填充已达到其容量的索引，但在索引树的较高级别中预留空间，以容纳至少一个额外的索引行。

注意： 
填充因子的值 0 和 100 在所有方面都是相同的。
 

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

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

有关详细信息，请参阅填充因子。

SORT_IN_TEMPDB = { ON | OFF }
指定是否在 tempdb 中存储临时排序结果。默认值为 OFF。

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

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

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

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

IGNORE_DUP_KEY = { ON | OFF }
指定对唯一聚集索引或唯一非聚集索引执行多行插入操作时出现重复键值的错误响应。默认值为 OFF。

ON
发出一条警告信息，且只有违反了唯一索引的行才会失败。

OFF
发出错误消息，并回滚整个 INSERT 事务。

IGNORE_DUP_KEY 设置仅适用于创建或重新生成索引后发生的插入操作。该设置在索引操作期间无效。

对于 XML 索引以及对视图创建的索引，IGNORE_DUP_KEY 不能设置为 ON。

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

STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否重新计算分发统计信息。默认值为 OFF。

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

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

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

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

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

DROP_EXISTING = { ON | OFF }
指定应删除并重新生成已命名的先前存在的聚集、非聚集索引或 XML 索引。默认值为 OFF。

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

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

使用 DROP_EXISTING 不能更改索引类型，即关系索引或 XML 索引。另外，不能将主 XML 索引重新定义为辅助 XML 索引，反之亦然。

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

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

注意： 
联机索引操作只适用于 SQL Server 2005 Enterprise Edition。
 

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

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

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

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

XML 索引

对局部临时表的索引

聚集索引，前提是基础表包含 LOB 数据类型：image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。

使用 LOB 数据类型列定义的非聚集索引。

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

如果表包含 LOB 数据类型，但索引定义中未使用这些列中的任何列作为键或非键（包含性）列，则可以联机创建非聚集索引。

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

ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允许行锁。默认值为 ON。

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

OFF
未使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允许页锁。默认值为 ON。

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

OFF
未使用页锁。

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

max_degree_of_parallelism 可以是：

1
取消生成并行计划。

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

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

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

注意： 
并行索引操作只适用于 SQL Server 2005 Enterprise Edition。
 

权限
若要执行 CREATE INDEX，至少需要对表或视图具有 ALTER 权限。默认情况下，此权限授予 sysadmin 固定服务器角色以及 db_ddladmin 和 db_owner 固定数据库角色。

示例
创建简单非聚集索引
以下示例为 Purchasing.ProductVendor 表的 VendorID 列创建非聚集索引。

 复制代码
USE AdventureWorks;
GO
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);
GO

 

B. 创建简单非聚集组合索引
以下示例为 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列创建非聚集组合索引。

 复制代码
USE AdventureWorks
GO
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

 

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

 复制代码
USE AdventureWorks;
GO
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);
GO

 

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

 复制代码
--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.
 

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

 复制代码
USE AdventureWorks;
GO
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。

 复制代码
USE AdventureWorks;
GO
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 索引约束，也不会将其中任何一行插入该表。

E. 使用 DROP_EXISTING 删除和重新创建索引
以下示例使用 DROP_EXISTING 选项在 Production.WorkOrder 表的 ProductID 列上删除并重新创建现有索引。还设置了 FILLFACTOR 和 PAD_INDEX 选项。

 复制代码
USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

 

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

 复制代码
USE AdventureWorks;
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

 

G. 创建具有包含性（非键）列的索引
以下示例创建具有一个键列 (PostalCode) 和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的非聚集索引。然后执行该索引覆盖的查询。若要显示查询优化器选择的索引，执行查询前请在 SQL Server Management Studio 中的“查询”菜单上选择“显示实际执行计划”。

 复制代码
USE AdventureWorks;
GO
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

 

H. 创建主 XML 索引
以下示例为 Production.ProductModel 表的 CatalogDescription 列创建主 XML 索引。

 复制代码
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

 

I. 创建辅助 XML 索引
以下示例为 Production.ProductModel 表的 CatalogDescription 列创建辅助 XML 索引。

 复制代码
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

 

J. 创建已分区索引
以下示例为现有分区方案 TransactionsPS1 创建非聚集已分区索引。此示例假定安装了已分区索引示例。有关安装信息，请参阅 Readme_PartitioningScript。

 复制代码
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO