设为首页收藏本站
开启辅助访问

CREATE INDEX (Transact

 您所在的位置：网站首页 › sql创建索引 › CREATE INDEX (Transact

CREATE INDEX (Transact

2023-07-23 15:40| 来源: 网络整理| 查看: 265

CREATE INDEX (Transact-SQL) 项目 06/06/2023

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

为表或视图创建相关索引。 也称为行存储索引，因为它可能是聚集或非聚集的 B 树索引。 可以在表中不存在数据时创建行存储索引。 使用行存储索引提高查询性能，尤其是在查询从特定列中进行选择或需要按特定顺序对值进行排序时。

注意

SQL Server 文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，SQL Server 实现了 B+ 树。 这不适用于列存储索引或内存中数据存储。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

Azure Synapse Analytics 和 Analytics Platform System (PDW) 目前不支持唯一约束。 任何引用唯一约束的示例仅适用于 SQL Server 和 SQL 数据库。

有关索引设计指南的信息，请参阅 SQL Server 索引设计指南。

示例：

对表或视图创建非聚集索引

CREATE INDEX index1 ON schema1.table1 (column1);

在表上创建聚集索引，并为表使用由 3 个部分组成的名称

CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);

使用唯一约束创建非聚集索引并指定排序顺序

CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);

主要方案：

从 SQL Server 2016 (13.x) 和 SQL 数据库开始，可针对列存储索引使用非聚集索引来提高数据仓库查询性能。 有关详细信息，请参阅列存储索引 - 数据仓库。

有关其他类型的索引，请参阅：

CREATE XML INDEX CREATE SPATIAL INDEX CREATE COLUMNSTORE INDEX

Transact-SQL 语法约定

语法 适用于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例的语法 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [ WHERE ] [ WITH ( [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ ; ] ::= { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name } ::= { 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 | OFF } | MAX_DURATION = [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 ( { | } [ , ...n ] ) ] | XML_COMPRESSION = { ON | OFF } [ ON PARTITIONS ( { | } [ , ...n ] ) ] } ::= [ AND ] [ ...n ] ::= | ::= column_name IN (constant ,...n) ::= column_name constant ::= { IS | IS NOT | = | | != | > | >= | !> | < | '20000101' AND EndDate 1 基于当前系统工作负荷，将并行索引操作中使用的最大处理器数限制为指定数量或更少。

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

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

注意

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

DATA_COMPRESSION

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

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

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

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

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

XML_COMPRESSION

适用于：SQL Server 2022 (16.x) 及更高版本和 Azure SQL 数据库。

为包含一个或多个 xml 数据类型列的指定索引指定 XML 压缩选项。 选项如下：

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

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

ON PARTITIONS ( { | } [ ,...n ] )

指定应用 DATA_COMPRESSION 或 XML_COMPRESSION 设置的分区。 如果未对索引进行分区，则 ON PARTITIONS 参数将生成错误。 如果未提供 ON PARTITIONS 子句，DATA_COMPRESSION 或 XML_COMPRESSION 选项将应用于已分区索引的所有分区。

注意

XML_COMPRESSION 仅从 SQL Server 2022 (16.x) 及更高版本和 Azure SQL 数据库预览版起可用。

可以通过下列方式指定 ：

提供分区号（例如：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) );

还可以多次指定 XML_COMPRESSION 选项，例如：

REBUILD WITH ( XML_COMPRESSION = OFF ON PARTITIONS (1), XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8), XML_COMPRESSION = OFF ON PARTITIONS (3, 5) ); 备注

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

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

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

创建主键时，聚集索引可以基于表变量。 在查询完成或会话结束时，将删除索引。

索引支持扩展属性。

CREATE INDEX 在 Microsoft Fabric 中不受支持。

聚集索引

对表（堆）创建聚集索引或删除和重新创建现有聚集索引时，要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。 有关聚集索引的详细信息，请参阅创建聚集索引和 SQL Server 索引体系结构和设计指南。

“非聚集索引”

从 SQL Server 2016 (13.x) 开始和在 Azure SQL 数据库 中，可以为作为聚集列存储索引进行存储的表创建非聚集索引。 如果首先为作为堆或聚集索引进行存储的表创建非聚集索引，当后期将该表转换为聚集列存储索引时，该索引将会保留。 重新生成聚集列存储索引也不需要删除非聚集索引。

限制和局限：

为作为聚集列存储索引进行存储的表创建非聚集索引时，FILESTREAM_ON 选项无效。 唯一索引

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

已分区索引

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

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

警告

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

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

可以使用与为表创建索引时相同的方式，为已分区表创建索引视图。 有关聚集索引的详细信息，请参阅分区表和索引以及 SQL Server 索引体系结构和设计指南。

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

筛选索引

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

筛选索引所需的 SET 选项

如果下列任何条件成立，则需要“必需的值”列中的 SET 选项：

创建筛选索引。

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

查询优化器使用该筛选索引生成查询计划。

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 当数据库兼容级别设置为 90 或更高时，如果将 ANSI_WARNINGS 设置为 ON，则将使 ARITHABORT 隐式设置为 ON。 如果数据库兼容性级别设置为 80 或更低，则必须将 ARITHABORT 选项显式设置为 ON。

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

不会创建筛选索引。 数据库引擎生成错误并回滚对索引中的数据进行更改的 INSERT、UPDATE、DELETE 或 MERGE 语句。 查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。

有关筛选索引的详细信息，请参阅创建筛选索引和 SQL Server 索引体系结构和设计指南。

空间索引

有关空间索引的信息，请参阅 CREATE SPATIAL INDEX 和空间索引概述。

XML 索引

有关 XML 索引的信息，请参阅 CREATE XML INDEX 和 XML 索引 (SQL Server)。

索引键大小

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

非聚集索引可以在索引的叶级别包含非键列。 计算索引键大小时，数据库引擎不考虑这些列。 有关详细信息，请参阅创建带有包含列的索引和 SQL Server 索引体系结构和设计指南。

注意

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

计算列

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

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

基于 Transact-SQL 和 CLR 函数以及由用户标记为确定性的 CLR 用户定义类型方法的计算列。 基于数据库引擎定义为确定性但不精确的表达式的计算列。

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

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 索引体系结构和设计指南。

指定索引选项

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

只能使用 WITH ( = ) 指定新的索引选项。 指定选项时不能在同一语句中同时使用向后兼容语法和新语法。 例如，指定 WITH (DROP_EXISTING, ONLINE = ON) 会导致语句失败。 在创建 XML 索引时，必须使用 WITH ( = ) 指定选项。 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 选项

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

不能在执行联机索引操作的过程中更改、截断或删除基础表。 索引操作期间需要额外的临时磁盘空间。 可以对分区索引以及包含持久性计算列或包含列的索引执行联机操作。 通过 low_priority_lock_wait 参数选项可以确定在 Sch-M 锁上阻塞时，索引操作如何才能继续。

有关详细信息，请参阅 Perform Index Operations Online。

资源

可恢复联机索引创建操作需要以下资源：

使索引保持生成所需的附加空间，包括索引暂停的时间 排序阶段中的额外日志吞吐量。 与常规联机索引创建相比，可恢复索引的总体日志空间使用率较低，并允许在此操作期间进行日志截断。 阻止任何 DDL 修改的 DDL 状态 在操作期间暂停时和运行操作时都会阻止对内置索引进行虚影清除。 当前功能限制

对于可恢复索引创建操作会禁用以下功能：

可恢复联机索引创建操作暂停后，不能更改 MAXDOP 的初始值

创建一个索引，其中包含：

已计算或 TIMESTAMP 列作为键列 LOB 列作为包含列的可恢复索引创建 筛选索引 可恢复索引操作

适用对象：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

下列指南适用于可恢复索引操作：

联机索引创建可使用 RESUMABLE = ON 选项指定为可恢复。 RESUMABLE 选项对于给定索引在元数据不持久，并且仅适用于当前 DDL 语句的持续时间。 因此，必须显式指定 RESUMABLE = ON 子句才能启用可恢复性。 仅 RESUMABLE = ON 选项支持 MAX_DURATION 选项。 用于 RESUMABLE 选项的 MAX_DURATION 为生成的索引指定时间间隔。 使用此时间之后，索引生成会暂停或完成其执行。 由用户确定何时可以恢复暂停的索引的生成。 MAX_DURATION 的时间（以分钟为单位）必须大于 0 分钟，且小于等于一周（7 * 24 * 60 = 10080 分钟）。 让索引操作长时间暂停可能会影响特定表的 DML 性能以及数据库磁盘容量，因为原始索引和新创建的索引需要磁盘空间并且需要在 DML 操作期间更新。 如果省略 MAX_DURATION 选项，则索引操作会继续，直到其完成或发生失败。 若要立即暂停索引操作，则可以停止 (Ctrl-C) 正在进行的命令，执行 ALTER INDEX PAUSE 命令或执行 KILL 命令。 暂停命令之后，可以使用 ALTER INDEX 命令恢复它。 重新执行可恢复索引的原始 CREATE INDEX 语句，会自动恢复暂停的索引创建操作。 可恢复索引不支持 SORT_IN_TEMPDB = ON 选项。 具有 RESUMABLE = ON 的 DDL 命令无法在显式事务（不能属于 begin TRAN ... COMMIT 块）中执行。 若要恢复/中止索引创建/重新生成，请使用 ALTER INDEX T-SQL 语法

注意

DDL 命令会运行到完成、暂停或失败。 如果命令暂停，则会发出错误，指示操作已暂停并且索引创建未完成。 可以从 sys.index_resumable_operations 获取有关当前索引状态的详细信息。 如同之前一样，发生失败时，也会发出错误。

若要指示索引创建作为可恢复操作执行并检查其当前执行状态，请参阅 sys.index_resumable_operations。

具有联机索引操作的 WAIT_AT_LOW_PRIORITY

适用于：此 CREATE INDEX 语法目前仅适用于 SQL Server 2022 (16.x)、Azure SQL 数据库和 Azure SQL 托管实例。 对于 ALTER INDEX，此语法适用于 SQL Server（从 SQL Server 2014 (12.x) 开始）和 Azure SQL 数据库。 有关详细信息，请参阅 ALTER INDEX。

可通过 low_priority_lock_wait 语法指定 WAIT_AT_LOW_PRIORITY 行为。 WAIT_AT_LOW_PRIORITY 只能与 ONLINE=ON 一起使用。

通过 WAIT_AT_LOW_PRIORITY 选项，DBA 可管理联机索引创建需要的 Sch-S 锁和 Sch-M 锁，还可选择 3 个选项之一。 在所有 3 种情况下，如果等待期间 (MAX_DURATION = n [minutes]) 没有阻塞活动，则联机索引重新生成会立即执行，而不等待 DDL 语句完成。

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

MAX_DURATION = time [MINUTES]

联机索引创建锁将在执行 DDL 命令时以低优先级等待的等待时间（以分钟为单位指定的整数值）。 如果操作被阻塞的时间达到 MAX_DURATION，将执行指定的 ABORT_AFTER_WAIT 操作。 MAX_DURATION 时间始终以分钟为单位，MINUTES 一词可以省略。

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

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

SELF 不采取任何操作，直接退出当前执行的联机索引创建 DDL 操作。 MAX_DURATION 等于 0 时，不能使用 SELF 选项。

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

行锁和页锁选项

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

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

顺序键

适用对象：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

最后一页插入争用是在以下情况下发生的常见性能问题：当大量并发线程尝试将行插入包含顺序键的索引时。 如果前导键列包含始终增加（或减少）的值（如标识列），或包含默认为当前日期/时间的日期，索引就会被视为顺序索引。 由于键是按顺序插入，因此所有新行都会插入到索引结构的末尾处，即位于同一页面上。 这会导致内存中出现页面争用，可观察到多个线程在 PAGELATCH_EX 上等待相关页面。

通过启用 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引选项，可以在数据库引擎内启用优化，有助于提高索引中高并发插入的吞吐量。 它适用于因包含顺序键而容易发生最后一页插入争用的索引，但可能也有助于在 B 树索引结构的其他区域中有作用点的索引。

注意

SQL Server 文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，SQL Server 实现了 B+ 树。 这不适用于列存储索引或内存中数据存储。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

查看索引信息

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

数据压缩

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

通过压缩可将更多的行存储在页上，但不能更改最大行大小。 对索引的非叶页不会进行页压缩，但可进行行压缩。 每个非聚集索引都有单独的压缩设置，并且不会继承基础表的压缩设置。 对堆创建聚集索引时，聚集索引会继承该堆的压缩状态，除非指定了另一压缩状态。

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

如果表有非对齐索引，无法更改单个分区的压缩设置。 ALTER INDEX ... REBUILD PARTITION ... 语法可重新生成索引的指定分区。 ALTER INDEX ... REBUILD WITH ... 语法可重新生成索引的所有分区。

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

XML 压缩

适用于：SQL Server 2022 (16.x) 及更高版本和 Azure SQL 数据库。

数据压缩的许多相同注意事项适用于 XML 压缩。 还应该注意以下事项：

指定分区列表时，可以在单个分区上启用 XML 压缩。 如果未指定分区列表，则所有分区都设置为启用 XML 压缩。 创建表或索引时，除非另行指定，否则将禁用 XML 数据压缩。 修改表时，除非指定了其他压缩设置，否则将保留现有压缩设置。 如果指定分区列表或分区超出范围，将生成错误。 对堆创建聚集索引时，聚集索引会继承该堆的 XML 压缩状态，除非指定了另一压缩选项。 若要更改堆的 XML 压缩设置，要求对表重新生成所有非聚集索引，以便它们具有指向堆中的新行位置的指针。 可以联机或脱机启用或禁用 XML 压缩功能。 当执行联机操作时，对堆启用压缩功能是单线程的。 若要确定已分区表中分区的 XML 压缩状态，请查询 sys.partitions 目录视图的 xml_compression 列。 权限

要求对表或视图具有 ALTER 权限，或者拥有 db_ddladmin 固定数据库角色的成员身份。

限制和局限

在 Azure Synapse Analytics和Analytics Platform System (PDW) 中不能执行以下创建：

当列存储索引已存在时，不能为数据仓库表创建聚集或非聚集行存储索引。 此行为与 SMP SQL Server 的行为不同，后者允许同一表中同时存在行存储和列存储索引。 不能对视图创建索引。 元数据

若要查看现有索引的信息，可以查询 sys.indexes 目录视图。

版本说明

SQL 数据库不支持文件组和文件流选项。

示例：所有版本。 使用 AdventureWorks 数据库 A. 创建简单的非聚集行存储索引

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

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. 创建简单的非聚集行存储组合索引

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

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD); C. 为其他数据库中的表创建索引

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID); D. 将列添加到索引

以下示例在 dbo.FactFinance 表中创建具有两列的 IX_FF 索引。 下一个语句重新生成具有三列的索引并保留现有名称。

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 数据库 E. 创建唯一非聚集索引

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

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. F. 使用 IGNORE_DUP_KEY 选项

以下示例首先在该选项设置为 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 索引约束，也不会将其中任何一行插入该表。

G. 使用 DROP_EXISTING 删除和重新创建索引

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

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID ON Production.WorkOrder(ProductID) WITH (FILLFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON); GO H. 为视图创建索引

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

-- 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. 创建具有包含列（非键列）的索引

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

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. 创建已分区索引

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

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID ON Production.TransactionHistory (ReferenceOrderID) ON TransactionsPS1 (TransactionDate); GO K. 创建筛选索引

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

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate" ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL; L. 创建压缩索引

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

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 使用页压缩并对索引的分区 2 至 4 使用行压缩来创建对已分区表的索引。

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. 使用 XML 压缩创建索引

适用于：SQL Server 2022 (16.x) 及更高版本和 Azure SQL 数据库。

下面的示例将使用 XML 压缩对无分区表创建索引。 索引中至少有一列必须是 xml 数据类型。

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

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

CREATE CLUSTERED INDEX IX_PartTab2Col1 ON PartitionTable1 (Col1) WITH (XML_COMPRESSION = ON); GO N. 创建、恢复、暂停和中止可恢复索引操作

适用对象：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

-- 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; O. 具有不同低优先级锁选项的 CREATE INDEX

下面的示例使用 WAIT_AT_LOW_PRIORITY 选项来指定用于处理阻塞的不同策略。

--Kill this session after waiting 5 minutes CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF))); GO --Kill blocker sessions CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS))); GO

下面的示例使用 RESUMABLE 选项并指定两个 MAX_DURATION 值，第一个应用于 ABORT_AFTER_WAIT 选项，第二个应用于 RESUMABLE 选项。

--With resumable option; default locking behavior CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES); 示例：Azure Synapse Analytics 和 Analytics Platform System (PDW) P. 基本语法

创建、恢复、暂停和中止可恢复索引操作

适用对象：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

-- 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; Q. 为当前数据库中的表创建非聚集索引

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

CREATE INDEX IX_ProductVendor_VendorID ON ProductVendor (VendorID); R. 为其他数据库中的表创建聚集索引

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID); S. 在表上创建有序的聚集索引

下面的示例在 MyDB 数据库的 T1 表上的 c1 和 c2 列创建有序的聚集索引。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 ORDER (c1, c2); T. 在表上将 CCI 转换为有序的聚集索引

下面的示例将现有的聚集列存储索引转换为有序聚集列存储索引，该索引名为 MyOrderedCCI，位于 MyDB 数据库的 T2 表上的 c1 和 c2 列。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2 ORDER (c1, c2) WITH (DROP_EXISTING = ON); 另请参阅 SQL Server 索引体系结构和设计指南 联机执行索引操作 索引和 ALTER TABLE ALTER INDEX CREATE PARTITION FUNCTION CREATE PARTITION SCHEME CREATE SPATIAL INDEX CREATE STATISTICS CREATE TABLE CREATE XML INDEX 数据类型 DBCC SHOW_STATISTICS DROP INDEX XML 索引 (SQL Server) sys.indexes sys.index_columns sys.xml_indexes EVENTDATA


【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3