关于对视图创建索引的一些问题

今天晚上在网上找了一些关于对视图创建索引的文章，比较不错，发上来：

第一篇 聚集索引与非聚集索引

索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询的速度

按照存储方式分为：聚集与非聚集索引
按照维护与管理索引角度分为：唯一索引、复合索引和系统自动创建的索引

索引的结构是由：根节点--->非叶节点--->非叶节点--->叶节点

1、聚集索引:表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大。

创建聚集索引的语法：
   create NONCLUSTERED INDEX idximpID ON EMP(empID)

２、非聚集索引:不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/修改/删除的影响很少
create CLUSTERED INDEX idxempID on emp(empID)

３、惟一索引：惟一索引可以确保索引列不包含重复的值.可以用多个列，但是索引可以确保索引列中每个值组合都是唯一的
create unique index idxempid on emp(姓，名)

4、复合索引：如果在两上以上的列上创建一个索引，则称为复合索引。
那么，不可能有两行的姓和名是重复的
语法：
create index indxfullname on addressbook(firstname,lastname)
5、系统自建的索引:在使用T_sql语句创建表的时候使用PRIMARY KEY或UNIQUE约束时，会在表上自动创建一个惟一索引
自动创建的索引是无法删除的
create table ABc
( empID int primary key,
   firstname varchar(50)UNIQUE,
   lastname   varchar(50)UNIQUE,
)
这样的结果就出来了三个索引，但只有一个聚集索引哦

问题：聚集与非聚集的排序是否用变化？
答案是：聚集的有变化，非聚集的排序与未创建索引之前一样

引用地址：http://hi.baidu.com/jwh166/blog/item/ee8a982bb6cd00fae7cd4038.html

第二篇 使用 SQL Server 2000 索引视图提高性能

什么是索引视图？

许多年来，Microsoft? SQL Server? 一直都提供创建虚拟表（称为视图）的功能。在过去，这些视图主要有两种用途：

  1. 提供安全机制，将用户限制在一个或多个基表中的数据的某个子集。
  2. 提供一种机制，允许开发人员定制用户如何才能以逻辑方式查看存储在基表中的数据。

SQL Server 2000 已经扩展了 SQL Server 视图的功能，以提高系统性能。它可以在一个视图上创建唯一的群集索引和非群集索引，可以改进最复杂查询的数据访问性能。在 SQL Server 2000 中，拥有唯一群集索引的视图被称为索引视图。

注意：   索引视图只是 SQL Server 2000 企业版和 SQL Server 2000 开发人员版的一个功能。

从数据库管理系统 (DBMS) 的观点来看，视图是数据（元数据）的说明。创建典型视图时，通过 SELECT 语句（定义一个显示为虚拟表的结果集）来定义元数据。当其它查询的 FROM 子句中引用了某个视图时，将从系统目录中检索该元数据，并对其进行扩展以代替该视图的引用。在视图扩展之后，查询优化器会为正在执行的查询编译单个执行计划。

如果是非索引视图，视图在运行时将被实体化。任何计算（如联接或聚合）都在为每个引用该视图的查询执行查询期间进行。（视图并不总需要被完全实体化。查询可以包含其它一些谓词、联接或聚合，以应用于该视图所引用的表和视图。）在视图上创建了唯一的群集索引之后，视图的结果集会立即被实体化并持续保存在数据库的物理存储空间中，以便节省这种操作所占用的大量资源。

在执行查询时，有两种方法可以使用索引视图。查询可直接引用索引视图，更重要的是，如果查询优化器确定视图能够替换为查询的部分或全部，而且这是低成本的查询计划，则可以选择索引视图。第二种情况是使用索引视图代替基础表及其普通索引。此时，不需要在查询中引用视图，查询优化器即可在执行查询期间使用该视图。这样，现有的应用程序无需更改即可从新建的索引视图中获益。

通过索引视图提高的性能

使用索引来提高查询性能并不是什么新观念，不过，索引视图还具有使用标准索引不能获得的其它性能优点。索引视图能够在以下方面提高查询性能：

  • 能够预先计算聚合并将其存储在索引中，从而最大限度地减少在执行查询期间进行成本很高的计算。
  • 能够预先联接表并存储生成的数据集。
  • 能够存储联接或聚合的组合。

下图说明了查询优化器使用索引视图时一般能够提高多少性能。提供的查询复杂程度各不相同（例如，聚合计算的数量、所用表的数量或谓词数），并包括来自实际生产环境的数百万行的大表。

使用视图的辅助索引

视图的辅助性非群集索引可以提高其它查询性能。与表的辅助索引类似，视图的辅助索引也可以提供更多选项，以便查询优化器在编译过程中从中进行选择。例如，如果查询包括群集索引未涉及的列，优化器可以在计划中选择一个或多个辅助索引，从而避免对索引视图或基表进行费时的全局扫描。

由于索引需要不断维护，所以为架构添加索引会增加数据库的额外开销。因此应该认真考虑，找到索引和维护额外开销之间的平衡点。

使用索引视图的好处

实现索引视图之前，请先分析数据库的工作量。运用自己对查询以及各种工具（例如 SQL 分析器）的知识来鉴别使用索引视图可以获益的查询。如果经常进行聚合和联接，最好使用索引视图。

并非所有查询都会从索引视图中获益。与普通索引类似，如果未使用索引视图，就没有好处可言。在此情况下，不但不能提高性能，还会加大磁盘空间的占用、增加维护和优化的成本。但是，如果使用了索引视图，它们可以（成数量级地）明显地提高数据访问的性能。这是因为查询优化器使用存储在索引视图中的预先计算的结果，从而大大降低了执行查询的成本。

查询优化器只在查询的成本比较大时才考虑使用索引视图。这样可以避免在查询优化成本超出因使用索引视图而节省的成本时，试图使用各种索引视图。当查询成本低于 1 时，几乎不使用索引视图。

使用索引视图可以受益的应用包括：

  • 决定支持工作量
  • 数据集市
  • 联机分析处理 (OLAP) 库和源
  • 数据挖掘工作量

从查询的类型和模式的角度来看，受益的应用可被归纳为包含以下内容的应用：

  • 大表的联接和聚合
  • 查询的重复模式
  • 重复聚合相同或重叠的列集
  • 针对相同关键字重复联接相同的表
  • 上述的组合

相反，包含许多写入的联机事务处理 (OLTP) 系统或更新频繁的数据库，可能会因为要同时更新视图和根本基表而使维护成本增加，所以不能利用索引视图。

查询优化器如何使用索引视图

SQL Server 查询优化器可自动确定何时可以将索引视图用于给定的查询执行中。查询中无需直接引用视图，优化器就可以将该视图用于查询执行计划。因此，无需对现有的应用程序本身进行任何更改，这些应用程序即可利用索引视图。唯一需要做的就是创建索引视图。

优化器的考虑因素

查询优化器会考虑几个条件来确定索引视图能涵盖部分查询还是整个查询。这些条件符合查询中的单个 FROM 子句并包含以下内容：

  • 查询 FROM 子句中的表必须是索引视图 FROM 子句中的表的超集。
  • 查询中的联接条件必须是视图中联接条件的超集。
  • 查询中的聚合列必须是视图中的聚合列的子集。
  • 查询选择列表中的所有表达式都必须源自于视图选择列表或源自于不包括在视图定义中的表。
  • 查询搜索条件谓词必须是视图定义中搜索条件谓词的超集。视图搜索谓词中的每个合取项都必须以同样的形式出现在查询搜索谓词中。
  • 查询搜索条件谓词中的所有列（属于视图定义中的表）都必须出现在下列一项或多项中：
    • 视图定义中的同一个谓词。
    • GROUP BY 列表。
    • 视图选择列表（若没有 GROUP BY 列表）。

如果查询包含多个 FROM 子句（子查询、派生表、UNION），优化器可以选择多个索引视图来管理含有多个 FROM 子句的查询。

注意：   也存在例外情形，即优化器可能将两个 FROM 子句折叠成一个（将子查询折叠成联接或将派生表折叠成联接变体）。如果出现此类情况，索引视图替换可能会涵盖原查询中的多个 FROM 子句。

本文档结尾介绍了演示这些条件的查询示例。而建议的最佳方法就是：让查询优化器来确定在查询执行计划中使用哪些索引（如果有的话）。

使用 NOEXPAND 选项

NOEXPAND 选项强制查询优化器象对待包含群集索引的普通表一样对待视图。在此情况下，必须在 FROM 子句中直接引用索引视图。例如：

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND)WHERE ...

使用 EXPAND VIEWS 选项

另外，用户可以在查询结束时通过使用 EXPAND VIEWS 选项，明确地将索引视图排除在考虑之外。例如：

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...OPTION (EXPAND VIEWS)

如果使用该选项，查询优化器在评估低成本的方法（该方法涉及查询中引用的列）时将忽略所有视图索引。

设计的考虑因素

为数据库系统找到适当的索引集是相当复杂的。尽管在设计普通索引时要考虑许多可能性，但将索引视图添加到架构会极大地增加设计和潜在结果的复杂性。例如，索引视图可用于：

  • 查询中所引用表的任何子集。
  • 查询中条件的任何子集（属于表的上述子集）
  • 分组列。
  • 聚合函数，如 SUM。

应同时设计表的索引和索引视图，以便从各个结构中获得最佳结果。由于索引和索引视图都可能对给定的查询有用，所以单独设计它们会导致多余的建议方案，以致存储和维护开销较高。在调整数据库的物理设计时，必须均衡考虑各种查询集的性能要求与数据库系统必须支持的更新操作。因此，为索引视图找到一种合理的物理设计是一项很具挑战性的任务，因而应该尽可能地使用“索引微调向导”。

如果存在许多索引视图可供查询优化器考虑用于特定查询，查询优化成本会显著增加。查询优化器可能考虑为查询中表的任意子集定义的所有索引视图。拒绝每一个视图之前，必须对它进行语法分析，然后研究其是否可能成为潜在的替换体。这可能需要一些时间，尤其是在有数百个此类的视图用于给定的查询时。

视图必须符合几项要求，您才能为其创建唯一的群集索引。在设计阶段，请考虑以下要求：

  • 视图以及视图中引用的所有表都必须在同一数据库中，并具有同一个所有者。
  • 索引视图无需包含要供优化器使用的查询中引用的所有表。
  • 必须先为视图创建唯一群集索引，然后才可以创建其它索引。
  • 创建基表、视图和索引以及修改基表和视图中的数据时，必须正确设置某些 SET 选项（在本文档的后文中讨论）。另外，如果这些 SET 选项正确，查询优化器将不考虑索引视图。
  • 视图必须使用架构绑定创建，视图中引用的任何用户定义的函数必须使用 SCHEMABINDING 选项创建。
  • 另外，还要求有一定的磁盘空间来存放由索引视图定义的数据。

设计准则

设计索引视图时，请考虑以下准则：

  • 设计的索引视图必须能用于多个查询或多个计算。

    例如，包含某列的 SUM 和某列的 COUNT_BIG 的索引视图可用于包含函数 SUM、COUNT、COUNT_BIG 或 AVG 的查询。由于只需检索视图中的少数几行，而不是基表中的所有行，且执行 AVG 函数要求的部分计算已经完成，所以查询将比较快。

  • 使索引保持紧凑。

    通过使用最少的列数和尽可能少的字节数，优化器在查找行数据时可获得最高的效率。相反，如果定义了大的群集索引关键字，则为视图定义的任何辅助性非群集索引都将明显增大，这是因为非群集索引项除包含索引定义的列之外，还将包含群集关键字。

  • 考虑生成的索引视图的大小。

    在单纯的聚合情况下，如果索引视图的大小类似于原表的大小，使用索引视图可能无法明显提高任何性能。

  • 设计多个较小的索引视图来加快部分进程的速度。

    有时可能无法设计出能满足整个查询需要的索引视图。此时即可考虑创建这样一些索引视图，每个索引视图执行一部分查询。

    考虑以下示例：

    • 经常执行的查询会聚合一个数据库中的数据，再聚合另一个数据库中的数据，然后联接结果。由于索引视图不能引用多个数据库中的表，所以您不能设计一个视图来执行整个进程。不过，可以为要进行聚合的每个数据库创建索引视图。如果优化器能够将索引视图与现有查询相匹配，至少聚合处理将会因为不必记录现有查询而提高速度。尽管联接处理不会加快，整个查询的速度却因使用了存储在索引视图中的聚合而加快。
    • 经常执行的查询会聚合多个表中的数据，然后使用 UNION 来将结果结合起来。UNION 不允许在索引视图中使用。您可以设计一些视图来执行每个单独的聚合运算。然后优化器可以选择索引视图来加快查询的速度，而无需记录查询。尽管 UNION 处理没有改进，单个聚合进程却得以改进。

原文地址：http://ayeel.my.gsdn.net/2005/04/08/2694/

第三篇 为给定表或视图创建索引

CREATE INDEX
为给定表或视图创建索引。

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

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

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

参数
UNIQUE

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

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

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

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

CLUSTERED

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

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

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

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

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

NONCLUSTERED

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

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

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

index_name

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

table

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

view

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

若要在确定性视图中查找列，请使用 COLUMNPROPERTY 函数（IsDeterministic 属性）。该函数的 IsPrecise 属性可用来确定键列是否精确。

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

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

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

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

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

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

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

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

column

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

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

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

[ASC | DESC]

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

n

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

PAD_INDEX

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

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

FILLFACTOR = fillfactor

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

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

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

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

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

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

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

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

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

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

IGNORE_DUP_KEY

控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了 IGNORE_DUP_KEY，并且执行了创建重复键的 INSERT 语句，SQL Server 将发出警告消息并忽略重复的行。
例子：
create unique index Uq_sno on student(sno) with IGNORE_DUP_KEY
insert into student(sno,sname) values(95001,'a')--本语句插入的值忽略
insert into student(sno,sname) values(95012,'b')--正常执行
如果没有为索引指定 IGNORE_DUP_KEY，SQL Server 会发出一条警告消息，并回滚整个 INSERT 语句。
例子：
create unique index Uq_sno on student(sno)
insert into student(sno,sname) values(95001,'a')
insert into student(sno,sname) values(95012,'b')
服务器: 消息 2601，级别 14，状态 3，行 1
不能在具有唯一索引 'Uq_sno' 的对象 'student' 中插入重复键的行。
语句已终止。（两个insert都回退）

下表显示何时可使用 IGNORE_DUP_KEY。

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

DROP_EXISTING

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

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

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

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

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

STATISTICS_NORECOMPUTE

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

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

SORT_IN_TEMPDB

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

有关更多信息，请参见 tempdb 和索引创建。

ON filegroup

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

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

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

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

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

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

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

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

若要显示有关对象索引的报表，请执行 sp_helpindex。

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

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

在创建索引时，SQL Server 检查下列条件：

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

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

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

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

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

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

相反，如果创建表之后在计算列 c 上创建索引，则上述 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 ('1', '0')
GO

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

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

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

SELECT 列表中不得包含星号 (*)、'table.*' 通配符列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的计算列和标量聚合。

非聚合 SELECT 列表中不能包含表达式。聚合 SELECT 列表（包含 GROUP BY 的查询）中可能包含 SUM 和 COUNT_BIG(<expression>)；它一定包含 COUNT_BIG(*)。不允许有其它聚合函数（MIN、MAX、STDEV，...）。

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

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

索引视图受限于以下的附加限制：

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

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

在任何联接表中，均不允许进行 OUTER JOIN 操作。

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

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

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

引用地址：http://www.shujucn.cn/blog/article.asp?id=108

posted @ 2007-07-21 23:38  Athrun  阅读(765)  评论(0)  编辑  收藏  举报