sqlmysql创建视图索引_SQL语句创建索引

语法&＃xff1a;

CREATE [索引类型] INDEX 索引名称

ON 表名(列名)

WITH FILLFACTOR &＃61; 填充因子值0~100

GO

USE 库名

GO

IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME&＃61;&＃39;IX_TEST_TNAME&＃39;)--检测是否已经存在IX_TEST_TNAME索引

DROP INDEX TEST.IX_TEST_TNAME--如果存在则删除

--创建索引

CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引

ON TEST(TNAME) --为TEST表的TNAME字段创建索引

WITH FILLFACTOR &＃61; 30 --填充因子为30%

GO

SELECT * FROM TEST(INDEX &＃61; IX_TEST_TNAME) WHERE TNAME &＃61; &＃39;A&＃39; --指定按‘IX_TEST_TNAME’索引查询

总结&＃xff1a;1.什么是索引&＃xff1a;数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

2.分类&＃xff1a;

唯一索引(UNIQUE)&＃xff1a;不允许两行具有相同的索引值(创建了唯一约束&＃xff0c;系统将自动创建唯一索引)

主键索引&＃xff1a;主键索引要求主键中的每个值是唯一的&＃xff0c;(创建主键自动创建主键索引)

聚集索引(CLUSTERED)&＃xff1a;表中各行的物理顺序与键值的逻辑(索引)顺序相同&＃xff0c;表中只能包含一个聚集索引&＃xff0c;主键列默认为聚集索引

非聚集索引(NONCLUSTERED)&＃xff1a;表中各行的物理顺序与键值的逻辑(索引)顺序不匹配&＃xff0c;表中可以有249个非聚集索引

3.创建索引的标准&＃xff1a;用于频繁搜索的列&＃xff1b;用于对数据进行排序的列

注意&＃xff1a;如果表中仅有几行&＃xff0c;或列中只包含几个不同的值&＃xff0c;不推荐创建索引&＃xff0c;因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。

实际上&＃xff0c;您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引&＃xff1a;聚集索引(clustered index&＃xff0c;也称聚类索引、簇集索引)和非聚集索引(nonclustered index&＃xff0c;也称非聚类索引、非簇集索引)。下面&＃xff0c;我们举例来说明一下聚集索引和非聚集索引的区别&＃xff1a;

其实&＃xff0c;我们的汉语字典的正文本身就是一个聚集索引。比如&＃xff0c;我们要查“安”字&＃xff0c;就会很自然地翻开字典的前几页&＃xff0c;因为“安”的拼音是“an”&＃xff0c;而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的&＃xff0c;那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字&＃xff0c;那么就说明您的字典中没有这个字&＃xff1b;同样的&＃xff0c;如果查“张”字&＃xff0c;那您也会将您的字典翻到最后部分&＃xff0c;因为“张”的拼音是“zhang”。也就是说&＃xff0c;字典的正文部分本身就是一个目录&＃xff0c;您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您认识某个字&＃xff0c;您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字&＃xff0c;不知道它的发音&＃xff0c;这时候&＃xff0c;您就不能按照刚才的方法找到您要查的字&＃xff0c;而需要去根据“偏旁部首”查到您要找的字&＃xff0c;然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法&＃xff0c;比如您查“张”字&＃xff0c;我们可以看到在查部首之后的检字表中“张”的页码是672页&＃xff0c;检字表中“张”的上面是“驰”字&＃xff0c;但页码却是63页&＃xff0c;“张”的下面是“弩”字&＃xff0c;页面是390页。很显然&＃xff0c;这些字并不是真正的分别位于“张”字的上下方&＃xff0c;现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序&＃xff0c;是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字&＃xff0c;但它需要两个过程&＃xff0c;先找到目录中的结果&＃xff0c;然后再翻到您所需要的页码。

我们把这种目录纯粹是目录&＃xff0c;正文纯粹是正文的排序方式称为“非聚集索引”。

通过以上例子&＃xff0c;我们可以理解到什么是“聚集索引”和“非聚集索引”。

为给定表或视图创建索引。 只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引&＃xff0c;无论表中是否有数据。可以通过指定限定的数据库名称&＃xff0c;为另一个数据库中的表或视图创建索引。

语法 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH [ ,...n] ] [ ON filegroup ]

::&＃61; { PAD_INDEX | FILLFACTOR &＃61; fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }

参数 UNIQUE

为表或视图创建唯一索引(不允许存在索引值相同的两行)。视图上的聚集索引必须是 UNIQUE 索引。

在创建索引时&＃xff0c;如果数据已存在&＃xff0c;Microsoft? SQL Server? 会检查是否有重复值&＃xff0c;并在每次使用 INSERT 或 UPDATE 语句添加数据时进行这种检查。如果存在重复的键值&＃xff0c;将取消 CREATE INDEX 语句&＃xff0c;并返回错误信息&＃xff0c;给出第一个重复值。当创建 UNIQUE 索引时&＃xff0c;有多个 NULL 值被看作副本。

如果存在唯一索引&＃xff0c;那么会产生重复键值的 UPDATE 或 INSERT 语句将回滚&＃xff0c;SQL Server 将显示错误信息。即使 UPDATE 或 INSERT 语句更改了许多行但只产生了一个重复值&＃xff0c;也会出现这种情况。如果在有唯一索引并且指定了 IGNORE_DUP_KEY 子句情况下输入数据&＃xff0c;则只有违反 UNIQUE 索引的行才会失败。在处理 UPDATE 语句时&＃xff0c;IGNORE_DUP_KEY 不起作用。

SQL Server 不允许为已经包含重复值的列创建唯一索引&＃xff0c;无论是否设置了 IGNORE_DUP_KEY。如果尝试这样做&＃xff0c;SQL Server 会显示错误信息&＃xff1b;重复值必须先删除&＃xff0c;才能为这些列创建唯一索引。

CLUSTERED

创建一个对象&＃xff0c;其中行的物理排序与索引排序相同&＃xff0c;并且聚集索引的最低一级(叶级)包含实际的数据行。一个表或视图只允许同时有一个聚集索引。

具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引&＃xff0c;然后才能为该视图定义其它索引。

在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。

如果没有指定 CLUSTERED&＃xff0c;则创建非聚集索引。

说明 因为按照定义&＃xff0c;聚集索引的叶级与其数据页相同&＃xff0c;所以创建聚集索引时使用 ON filegroup 子句实际上会将表从创建该表时所用的文件移到新的文件组中。在特定的文件组上创建表或索引之前&＃xff0c;应确认哪些文件组可用并且有足够的空间供索引使用。文件组的大小必须至少是整个表所需空间的 1.2 倍&＃xff0c;这一点很重要。

NONCLUSTERED

创建一个指定表的逻辑排序的对象。对于非聚集索引&＃xff0c;行的物理排序独立于索引排序。非聚集索引的叶级包含索引行。每个索引行均包含非聚集键值和一个或多个行定位器(指向包含该值的行)。如果表没有聚集索引&＃xff0c;行定位器就是行的磁盘地址。如果表有聚集索引&＃xff0c;行定位器就是该行的聚集索引键。

每个表最多可以有 249 个非聚集索引(无论这些非聚集索引的创建方式如何&＃xff1a;是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建&＃xff0c;还是使用 CREATE INDEX 显式创建)。每个索引均可以提供对数据的不同排序次序的访问。

对于索引视图&＃xff0c;只能为已经定义了聚集索引的视图创建非聚集索引。因此&＃xff0c;索引视图中非聚集索引的行定位器一定是行的聚集键。

index_name

是索引名。索引名在表或视图中必须唯一&＃xff0c;但在数据库中不必唯一。索引名必须遵循标识符规则。

table

包含要创建索引的列的表。可以选择指定数据库和表所有者。

view

要建立索引的视图的名称。必须使用 SCHEMABINDING 定义视图才能在视图上创建索引。视图定义也必须具有确定性。如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性&＃xff0c;则视图也具有确定性。而且&＃xff0c;所有键列必须是精确的。只有视图的非键列可能包含浮点表达式(使用 float 数据类型的表达式)&＃xff0c;而且 float 表达式不能在视图定义的其它任何位置使用。

若要在确定性视图中查找列&＃xff0c;请使用 COLUMNPROPERTY 函数(IsDeterministic 属性)。该函数的 IsPrecise 属性可用来确定键列是否精确。

必须先为视图创建唯一的聚集索引&＃xff0c;才能为该视图创建非聚集索引。

在 SQL Server 企业版或开发版中&＃xff0c;查询优化器可使用索引视图加快查询的执行速度。要使优化程序考虑将该视图作为替换&＃xff0c;并不需要在查询中引用该视图。

在创建索引视图或对参与索引视图的表中的行进行操作时&＃xff0c;有 7 个 SET 选项必须指派特定的值。SET 选项 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、 ANSI_PADDING 和 ANSI_WARNING 必须为 ON。SET 选项 NUMERIC_ROUNDABORT 必须为 OFF。

如果与上述设置有所不同&＃xff0c;对索引视图所引用的任何表执行的数据修改语句 (INSERT、UPDATE、DELETE) 都将失败&＃xff0c;SQL Server 会显示一条错误信息&＃xff0c;列出所有违反设置要求的 SET 选项。此外&＃xff0c;对于涉及索引视图的 SELECT 语句&＃xff0c;如果任何 SET 选项的值不是所需的值&＃xff0c;则 SQL Server 在处理该 SELECT 语句时不考虑索引视图替换。在受上述 SET 选项影响的情况中&＃xff0c;这将确保查询结果的正确性。

如果应用程序使用 DB-Library 连接&＃xff0c;则必须为服务器上的所有 7 个 SET 选项指派所需的值。(默认情况下&＃xff0c;OLE DB 和 ODBC 连接已经正确设置了除 ARITHABORT 外所有需要的 SET 选项。)

如果并非所有上述 SET 选项均有所需的值&＃xff0c;则某些操作(例如 BCP、复制或分布式查询)可能无法对参与索引视图的表执行更新。在大多数情况下&＃xff0c;将 ARITHABORT 设置为 ON(通过服务器配置选项中的 user options)可以避免这一问题。

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

有关索引视图注意事项和限制的更多信息&＃xff0c;请参见注释部分。

column

应用索引的列。指定两个或多个列名&＃xff0c;可为指定列的组合值创建组合索引。在 table 后的圆括号中列出组合索引中要包括的列(按排序优先级排列)。

说明 由 ntext、text 或 image 数据类型组成的列不能指定为索引列。另外&＃xff0c;视图不能包括任何 text、ntext 或 image 列&＃xff0c;即使在 CREATE INDEX 语句中没有引用这些列。

当两列或多列作为一个单位搜索最好&＃xff0c;或者许多查询只引用索引中指定的列时&＃xff0c;应使用组合索引。最多可以有 16 个列组合到一个组合索引中。组合索引中的所有列必须在同一个表中。组合索引值允许的最大大小为 900 字节。也就是说&＃xff0c;组成组合索引的固定大小列的总长度不得超过 900 字节。有关组合索引中可变类型列的更多信息&＃xff0c;请参见注释部分。

[ASC | DESC]

确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。

n

表示可以为特定索引指定多个 columns 的占位符。

PAD_INDEX

指定索引中间级中每个页(节点)上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用&＃xff0c;因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默认情况下&＃xff0c;给定中间级页上的键集&＃xff0c;SQL Server 将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果为 FILLFACTOR 指定的百分比不够大&＃xff0c;无法容纳一行&＃xff0c;SQL Server 将在内部使用允许的最小值替代该百分比。

说明 中间级索引页上的行数永远都不会小于两行&＃xff0c;无论 FILLFACTOR 的值有多小。

FILLFACTOR &＃61; fillfactor

指定在 SQL Server 创建索引的过程中&＃xff0c;各索引页叶级的填满程度。如果某个索引页填满&＃xff0c;SQL Server 就必须花时间拆分该索引页&＃xff0c;以便为新行腾出空间&＃xff0c;这需要很大的开销。对于更新频繁的表&＃xff0c;选择合适的 FILLFACTOR 值将比选择不合适的 FILLFACTOR 值获得更好的更新性能。FILLFACTOR 的原始值将在 sysindexes 中与索引一起存储。

如果指定了 FILLFACTOR&＃xff0c;SQL Server 会向上舍入每页要放置的行数。例如&＃xff0c;发出 CREATE CLUSTERED INDEX ...FILLFACTOR &＃61; 33 将创建一个 FILLFACTOR 为 33% 的聚集索引。假设 SQL Server 计算出每页空间的 33% 为 5.2 行。SQL Server 将其向上舍入&＃xff0c;这样&＃xff0c;每页就放置 6 行。

说明 显式的 FILLFACTOR 设置只是在索引首次创建时应用。SQL Server 并不会动态保持页上可用空间的指定百分比。

用户指定的 FILLFACTOR 值可以从 1 到 100。如果没有指定值&＃xff0c;默认值为 0。如果 FILLFACTOR 设置为 0&＃xff0c;则只填满叶级页。可以通过执行 sp_configure 更改默认的 FILLFACTOR 设置。

只有不会出现 INSERT 或 UPDATE 语句时(例如对只读表)&＃xff0c;才可以使用 FILLFACTOR 100。如果 FILLFACTOR 为 100&＃xff0c;SQL Server 将创建叶级页 100% 填满的索引。如果在创建 FILLFACTOR 为 100% 的索引之后执行 INSERT 或 UPDATE&＃xff0c;会对每次 INSERT 操作以及有可能每次 UPDATE 操作进行页拆分。

如果 FILLFACTOR 值较小(0 除外)&＃xff0c;就会使 SQL Server 创建叶级页不完全填充的新索引。例如&＃xff0c;如果已知某个表包含的数据只是该表最终要包含的数据的一小部分&＃xff0c;那么为该表创建索引时&＃xff0c;FILLFACTOR 为 10 会是合理的选择。FILLFACTOR 值较小还会使索引占用较多的存储空间。

下表说明如何在已指定 FILLFACTOR 的情况下填充索引页。

FILLFACTOR 中间级页叶级页 0 一个可用项 100% 填满 1% -99 一个可用项 <&＃61; FILLFACTOR% 填满 100% 一个可用项 100% 填满

一个可用项是指页上可以容纳另一个索引项的空间。

重要 用某个 FILLFACTOR 值创建聚集索引会影响数据占用存储空间的数量&＃xff0c;因为 SQL Server 在创建聚集索引时会重新分布数据。

IGNORE_DUP_KEY

控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了 IGNORE_DUP_KEY&＃xff0c;并且执行了创建重复键的 INSERT 语句&＃xff0c;SQL Server 将发出警告消息并忽略重复的行。

如果没有为索引指定 IGNORE_DUP_KEY&＃xff0c;SQL Server 会发出一条警告消息&＃xff0c;并回滚整个 INSERT 语句。

下表显示何时可使用 IGNORE_DUP_KEY。

索引类型 选项 聚集 不允许 唯一聚集 允许使用 IGNORE_DUP_KEY 非聚集 不允许 唯一非聚集 允许使用 IGNORE_DUP_KEY

DROP_EXISTING

指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。因为非聚集索引包含聚集键&＃xff0c;所以在除去聚集索引时&＃xff0c;必须重建非聚集索引。如果重建聚集索引&＃xff0c;则必须重建非聚集索引&＃xff0c;以便使用新的键集。

为已经具有非聚集索引的表重建聚集索引时(使用相同或不同的键集)&＃xff0c; DROP_EXISTING 子句可以提高性能。DROP_EXISTING 子句代替了先对旧的聚集索引执行 DROP INDEX 语句&＃xff0c;然后再对新的聚集索引执行 CREATE INDEX 语句的过程。非聚集索引只需重建一次&＃xff0c;而且还只是在键不同的情况下才需要。

如果键没有改变(提供的索引名和列与原索引相同)&＃xff0c;则 DROP_EXISTING 子句不会重新对数据进行排序。在必须压缩索引时&＃xff0c;这样做会很有用。

无法使用 DROP_EXISTING 子句将聚集索引转换成非聚集索引&＃xff1b;不过&＃xff0c;可以将唯一聚集索引更改为非唯一索引&＃xff0c;反之亦然。

说明 当执行带 DROP_EXISTING 子句的 CREATE INDEX 语句时&＃xff0c;SQL Server 假定索引是一致的(即索引没有损坏)。指定索引中的行应按 CREATE INDEX 语句中引用的指定键排序。

STATISTICS_NORECOMPUTE

指定过期的索引统计不会自动重新计算。若要恢复自动更新统计&＃xff0c;可执行没有 NORECOMPUTE 子句的 UPDATE STATISTICS。

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

SORT_IN_TEMPDB

指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。如果 tempdb 与用户数据库不在同一磁盘集&＃xff0c;则此选项可能减少创建索引所需的时间&＃xff0c;但会增加创建索引时使用的磁盘空间。

有关更多信息&＃xff0c;请参见 tempdb 和索引创建。

ON filegroup

在给定的 filegroup 上创建指定的索引。该文件组必须已经通过执行 CREATE DATABASE 或 ALTER DATABASE 创建。

注释 为表或索引分配空间时&＃xff0c;每次递增一个扩展盘区(8 个 8 KB 的页)。每填满一个扩展盘区&＃xff0c;就会再分配一个。如果表非常小或是空表&＃xff0c;其索引将使用单页分配&＃xff0c;直到向索引添加了 8 页后&＃xff0c;再转而进行扩展盘区分配。若要获得有关索引已分配和占用的空间数量的报表&＃xff0c;请使用 sp_spaceused。

创建聚集索引要求数据库中的可用空间大约为数据大小的 1.2 倍。该空间不包括现有表占用的空间&＃xff1b;将对数据进行复制以创建聚集索引&＃xff0c;旧的无索引数据将在索引创建完成后删除。使用 DROP_EXISTING 子句时&＃xff0c;聚集索引所需的空间数量与现有索引的空间要求相同。所需的额外空间可能还受指定的 FILLFACTOR 的影响。

在 SQL Server 2000 中创建索引时&＃xff0c;可以使用 SORT_IN_TEMPDB 选项指示数据库引擎在 tempdb 中存储中间索引排序结果。如果 tempdb 在不同于用户数据库所在的磁盘集上&＃xff0c;则此选项可能减少创建索引所需的时间&＃xff0c;但会增加用于创建索引的磁盘空间。除在用户数据库中创建索引所需的空间外&＃xff0c; tempdb 还必须有大约相同的额外空间来存储中间排序结果。有关更多信息&＃xff0c;请参见 tempdb 和索引创建。

CREATE INDEX 语句同其它查询一样优化。SQL Server 查询处理器可以选择扫描另一个索引&＃xff0c;而不是执行表扫描&＃xff0c;以节省 I/O 操作。在某些情况下&＃xff0c;可以不必排序。

在运行 SQL Server 企业管理器和程序员版的多处理器计算机上&＃xff0c;CREATE INDEX 自动使用多个处理器执行扫描和排序&＃xff0c;与其它查询的操作方式相同。执行一条 CREATE INDEX 语句所使用的处理器数由配置选项 max degree of parallelism 和当前的工作负荷决定。如果 SQL Server 检测到系统正忙&＃xff0c;则在开始执行语句之前&＃xff0c;CREATE INDEX 操作的并发程度会自动降低。

自上一次文件组备份以来受 CREATE INDEX 语句影响的全部文件组必须作为一个单位备份。有关文件和文件组备份的更多信息&＃xff0c;请参见 BACKUP。

备份和 CREATE INDEX 操作不相互防碍。如果正在进行备份&＃xff0c;则在完整日志记录模式中创建索引&＃xff0c;而这可能需要额外的日志空间。

若要显示有关对象索引的报表&＃xff0c;请执行 sp_helpindex。

可以为临时表创建索引。在除去表或终止会话时&＃xff0c;所有索引和触发器都将被除去。

索引中的可变类型列 索引键允许的最大大小为 900 字节&＃xff0c;不过 SQL Server 2000 允许在可能包含大量可变类型列的列上创建索引&＃xff0c;而这些列的最大大小超过 900 字节。

在创建索引时&＃xff0c;SQL Server 检查下列条件&＃xff1a;

所有参与索引定义的固定数据列的总长度必须小于或等于 900 字节。当所要创建的索引只由固定数据列构成时&＃xff0c;固定数据列的总计大小必须小于或等于 900 字节。否则将不能创建索引&＃xff0c;且 SQL Server 将返回错误。

如果索引定义由固定类型列和可变类型列组成&＃xff0c;且固定数据列满足前面的条件(小于或等于 900 字节)&＃xff0c;则 SQL Server 仍要检查可变类型列的总大小。如果可变类型列的最大大小与固定数据列大小的和大于 900 字节&＃xff0c;则 SQL Server 将创建索引&＃xff0c;不过将给用户返回警告消息以提醒用户&＃xff1a;如果随后在可变类型列上的插入或更新操作导致总大小超过 900 字节&＃xff0c;则操作将失败且用户将收到运行时错误。同样&＃xff0c;如果索引定义只由可变类型列组成&＃xff0c;且这些列的最大总大小大于 900 字节&＃xff0c;则 SQL Server 将创建索引&＃xff0c;不过将返回警告消息。 有关更多信息&＃xff0c;请参见索引键的最大值。

在计算列和视图上创建索引时的考虑 在 SQL Server 2000 中&＃xff0c;还可以在计算列和视图上创建索引。在视图上创建唯一聚集索引可以提高查询性能&＃xff0c;因为视图存储在数据库中的方式与具有聚集索引的表的存储方式相同。

UNIQUE 或 PRIMARY KEY 只要满足所有索引条件&＃xff0c;就可以包含计算列。具体说来就是&＃xff0c;计算列必须具有确定性、必须精确&＃xff0c;且不能包含 text、ntext 或 image 列。有关确定性的更多信息&＃xff0c;请参见确定性函数和非确定性函数。

在计算列或视图上创建索引可能导致前面产生的 INSERT 或 UPDATE 操作失败。当计算列导致算术错误时可能产生这样的失败。例如&＃xff0c;虽然下表中的计算列 c 将导致算术错误&＃xff0c;但是 INSERT 语句仍有效&＃xff1a;

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

相反&＃xff0c;如果创建表之后在计算列 c 上创建索引&＃xff0c;则上述 INSERT 语句将失败。

CREATE TABLE t1 (a int, b int, c AS a/b) GO CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c GO INSERT INTO t1 VALUES (&＃39;1&＃39;, &＃39;0&＃39;) GO

在通过数字或 float 表达式定义的视图上使用索引所得到的查询结果&＃xff0c;可能不同于不在视图上使用索引的类似查询所得到的结果。这种差异可能是由对基础表进行 INSERT、DELETE 或 UPDATE 操作时的舍入错误引起的。

若要防止 SQL Server 使用索引视图&＃xff0c;请在查询中包含 OPTION (EXPAND VIEWS) 提示。此外&＃xff0c;任何所列选项设置不正确均会阻止优化程序使用视图上的索引。有关 OPTION (EXPAND VIEWS) 提示的更多信息&＃xff0c;请参见 SELECT。

对索引视图的限制 定义索引视图的 SELECT 语句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 关键字。也不能包含子查询。

SELECT 列表中不得包含星号 (*)、&＃39;table.*&＃39; 通配符列表、DISTINCT、COUNT(*)、COUNT()、基表中的计算列和标量聚合。

非聚合 SELECT 列表中不能包含表达式。聚合 SELECT 列表(包含 GROUP BY 的查询)中可能包含 SUM 和 COUNT_BIG()&＃xff1b;它一定包含 COUNT_BIG(*)。不允许有其它聚合函数(MIN、MAX、STDEV&＃xff0c;...)。

使用 AVG 的复杂聚合无法参与索引视图的 SELECT 列表。不过&＃xff0c;如果查询使用这样的聚合&＃xff0c;则优化程序将能使用该索引视图&＃xff0c;用 SUM 和 COUNT_BIG 的简单聚合组合代替 AVG。

若某列是从取值为 float 数据类型或使用 float 表达式进行取值的表达式得到的&＃xff0c;则不能作为索引视图或表中计算列的索引键。这样的列被视为是不精确的。使用 COLUMNPROPERTY 函数决定特定计算列或视图中的列是否精确。

索引视图受限于以下的附加限制&＃xff1a;

索引的创建者必须拥有表。所有表、视图和索引必须在同一数据库中创建。

定义索引视图的 SELECT 语句不得包含视图、行集函数、行内函数或派生表。同一物理表在该语句中只能出现一次。

在任何联接表中&＃xff0c;均不允许进行 OUTER JOIN 操作。

搜索条件中不允许使用子查询或者 CONTAINS 或 FREETEXT 谓词。

如果视图定义包含 GROUP BY 子句&＃xff0c;则视图的 SELECT 列表中必须包含所有分组依据列及 COUNT_BIG(*) 表达式。此外&＃xff0c;CREATE UNIQUE CLUSTERED INDEX 子句中必须只包含这些列。 可以创建索引的视图的定义主体必须具有确定性且必须精确&＃xff0c;这类似于计算列上的索引要求。请参见在计算列上创建索引。

权限 CREATE INDEX 的权限默认授予 sysadmin 固定服务器角色、db_ddladmin 和 db_owner 固定数据库角色和表所有者且不能转让。

示例 A. 使用简单索引 下面的示例为 authors 表的 au_id 列创建索引。

SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;au_id_ind&＃39;) DROP INDEX authors.au_id_ind GO USE pubs CREATE INDEX au_id_ind ON authors (au_id) GO

B. 使用唯一聚集索引 下面的示例为 emp_pay 表的 employeeID 列创建索引&＃xff0c;并且强制唯一性。因为指定了 CLUSTERED 子句&＃xff0c;所以该索引将对磁盘上的数据进行物理排序。

SET NOCOUNT ON USE pubs IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME &＃61; &＃39;emp_pay&＃39;) DROP TABLE emp_pay GO USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;employeeID_ind&＃39;) DROP INDEX emp_pay.employeeID_ind GO USE pubs GO CREATE TABLE emp_pay ( employeeID int NOT NULL, base_pay money NOT NULL, commission decimal(2, 2) NOT NULL ) INSERT emp_pay VALUES (1, 500, .10) INSERT emp_pay VALUES (2, 1000, .05) INSERT emp_pay VALUES (3, 800, .07) INSERT emp_pay VALUES (5, 1500, .03) INSERT emp_pay VALUES (9, 750, .06) GO SET NOCOUNT OFF CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay (employeeID) GO

C. 使用简单组合索引 下面的示例为 order_emp 表的 orderID 列和 employeeID 列创建索引。

SET NOCOUNT ON USE pubs IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME &＃61; &＃39;order_emp&＃39;) DROP TABLE order_emp GO USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;emp_order_ind&＃39;) DROP INDEX order_emp.emp_order_ind GO USE pubs GO CREATE TABLE order_emp ( orderID int IDENTITY(1000, 1), employeeID int NOT NULL, orderdate datetime NOT NULL DEFAULT GETDATE(), orderamount money NOT NULL )

INSERT order_emp (employeeID, orderdate, orderamount) VALUES (5, &＃39;4/12/98&＃39;, 315.19) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (5, &＃39;5/30/98&＃39;, 1929.04) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (1, &＃39;1/03/98&＃39;, 2039.82) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (1, &＃39;1/22/98&＃39;, 445.29) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (4, &＃39;4/05/98&＃39;, 689.39) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (7, &＃39;3/21/98&＃39;, 1598.23) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (7, &＃39;3/21/98&＃39;, 445.77) INSERT order_emp (employeeID, orderdate, orderamount) VALUES (7, &＃39;3/22/98&＃39;, 2178.98) GO SET NOCOUNT OFF CREATE INDEX emp_order_ind ON order_emp (orderID, employeeID)

D. 使用 FILLFACTOR 选项 下面的示例使用 FILLFACTOR 子句&＃xff0c;将其设置为 100。FILLFACTOR 为 100 将完全填满每一页&＃xff0c;只有确定表中的索引值永远不会更改时&＃xff0c;该选项才有用。

SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;zip_ind&＃39;) DROP INDEX authors.zip_ind GO USE pubs GO CREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR &＃61; 100

E. 使用 IGNORE_DUP_KEY 下面的示例为 emp_pay 表创建唯一聚集索引。如果输入了重复的键&＃xff0c;将忽略该 INSERT 或 UPDATE 语句。

SET NOCOUNT ON USE pubs IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME &＃61; &＃39;emp_pay&＃39;) DROP TABLE emp_pay GO USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;employeeID_ind&＃39;) DROP INDEX emp_pay.employeeID_ind GO USE pubs GO CREATE TABLE emp_pay ( employeeID int NOT NULL, base_pay money NOT NULL, commission decimal(2, 2) NOT NULL ) INSERT emp_pay VALUES (1, 500, .10) INSERT emp_pay VALUES (2, 1000, .05) INSERT emp_pay VALUES (3, 800, .07) INSERT emp_pay VALUES (5, 1500, .03) INSERT emp_pay VALUES (9, 750, .06) GO SET NOCOUNT OFF GO CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay(employeeID) WITH IGNORE_DUP_KEY

F. 使用 PAD_INDEX 创建索引 下面的示例为 authors 表中的作者标识号创建索引。没有 PAD_INDEX 子句&＃xff0c;SQL Server 将创建填充 10% 的叶级页&＃xff0c;但是叶级之上的页几乎被完全填满。使用 PAD_INDEX 时&＃xff0c;中间级页也填满 10%。

说明 如果没有指定 PAD_INDEX&＃xff0c;唯一聚集索引的索引页上至少会出现两项。

SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name &＃61; &＃39;au_id_ind&＃39;) DROP INDEX authors.au_id_ind GO USE pubs CREATE INDEX au_id_ind ON authors (au_id) WITH PAD_INDEX, FILLFACTOR &＃61; 10

G. 为视图创建索引 下面的示例将创建一个视图&＃xff0c;并为该视图创建索引。然后&＃xff0c;引入两个使用该索引视图的查询。

USE Northwind GO

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

--Create view. CREATE VIEW V1 WITH SCHEMABINDING AS SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM dbo.[Order Details] od, dbo.Orders o WHERE od.OrderID&＃61;o.OrderID GROUP BY OrderDate, ProductID GO

--Create index on the view. CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID) GO

--This query will use the above indexed view. SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID FROM dbo.[Order Details] od, dbo.Orders o WHERE od.OrderID&＃61;o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34) AND OrderDate >&＃61; &＃39;05/01/1998&＃39; GROUP BY OrderDate, ProductID ORDER BY Rev DESC

--This query will use the above indexed view. SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev FROM dbo.[Order Details] od, dbo.Orders o WHERE od.OrderID&＃61;o.OrderID AND DATEPART(mm,OrderDate)&＃61; 3 AND DATEPART(yy,OrderDate) &＃61; 1998 GROUP BY OrderDate ORDER BY OrderDate ASC