|
为给定表或视图创建索引 只有表或视图的所有者才能为表创建索引表或视图的所有者可以随时创建索引无论表中是否有数据可以通过指定限定的数据库名称为另一个数据库中的表或视图创建索引 语法 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 语句并返回错误信息给出第一个重复值当创建 UNIQUE 索引时有多个 NULL 值被看作副本 如果存在唯一索引那么会产生重复键值的 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 子句实际上会将表从创建该表时所用的文件移到新的文件组中在特定的文件组上创建表或索引之前应确认哪些文件组可用并且有足够的空间供索引使用文件组的大小必须至少是整个表所需空间的 倍这一点很重要 NONCLUSTERED 创建一个指定表的逻辑排序的对象对于非聚集索引行的物理排序独立于索引排序非聚集索引的叶级包含索引行每个索引行均包含非聚集键值和一个或多个行定位器（指向包含该值的行）如果表没有聚集索引行定位器就是行的磁盘地址如果表有聚集索引行定位器就是该行的聚集索引键 每个表最多可以有 个非聚集索引（无论这些非聚集索引的创建方式如何是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建还是使用 CREATE INDEX 显式创建）每个索引均可以提供对数据的不同排序次序的访问 对于索引视图只能为已经定义了聚集索引的视图创建非聚集索引因此索引视图中非聚集索引的行定位器一定是行的聚集键 index_name 是索引名索引名在表或视图中必须唯一但在数据库中不必唯一索引名必须遵循标识符规则 table 包含要创建索引的列的表可以选择指定数据库和表所有者 view 要建立索引的视图的名称必须使用 SCHEMABINDING 定义视图才能在视图上创建索引视图定义也必须具有确定性如果选择列表中的所有表达式WHERE 和 GROUP BY 子句都具有确定性则视图也具有确定性而且所有键列必须是精确的只有视图的非键列可能包含浮点表达式（使用 float 数据类型的表达式）而且 float 表达式不能在视图定义的其它任何位置使用 若要在确定性视图中查找列请使用 COLUMNPROPERTY 函数（IsDeterministic 属性）该函数的 IsPrecise 属性可用来确定键列是否精确 必须先为视图创建唯一的聚集索引才能为该视图创建非聚集索引 在 SQL Server 企业版或开发版中查询优化器可使用索引视图加快查询的执行速度要使优化程序考虑将该视图作为替换并不需要在查询中引用该视图 在创建索引视图或对参与索引视图的表中的行进行操作时有 个 SET 选项必须指派特定的值SET 选项 ARITHABORTCONCAT_NULL_YIELDS_NULLQUOTED_IDENTIFIERANSI_NULLSANSI_PADDING 和 ANSI_WARNING 必须为 ONSET 选项 NUMERIC_ROUNDABORT 必须为 OFF 如果与上述设置有所不同对索引视图所引用的任何表执行的数据修改语句 (INSERTUPDATEDELETE) 都将失败SQL Server 会显示一条错误信息列出所有违反设置要求的 SET 选项此外对于涉及索引视图的 SELECT 语句如果任何 SET 选项的值不是所需的值则 SQL Server 在处理该 SELECT 语句时不考虑索引视图替换在受上述 SET 选项影响的情况中这将确保查询结果的正确性 如果应用程序使用 DBLibrary 连接则必须为服务器上的所有 个 SET 选项指派所需的值（默认情况下OLE DB 和 ODBC 连接已经正确设置了除 ARITHABORT 外所有需要的 SET 选项） 如果并非所有上述 SET 选项均有所需的值则某些操作（例如 BCP复制或分布式查询）可能无法对参与索引视图的表执行更新在大多数情况下将 ARITHABORT 设置为 ON（通过服务器配置选项中的 user options）可以避免这一问题 强烈建议在服务器的任一数据库中创建计算列上的第一个索引视图或索引后尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON 有关索引视图注意事项和限制的更多信息请参见注释部分 column 应用索引的列指定两个或多个列名可为指定列的组合值创建组合索引在 table 后的圆括号中列出组合索引中要包括的列（按排序优先级排列） 说明由 ntexttext 或 image 数据类型组成的列不能指定为索引列另外视图不能包括任何 textntext 或 image 列即使在 CREATE INDEX 语句中没有引用这些列 当两列或多列作为一个单位搜索最好或者许多查询只引用索引中指定的列时应使用组合索引最多可以有 个列组合到一个组合索引中组合索引中的所有列必须在同一个表中组合索引值允许的最大大小为 字节也就是说组成组合索引的固定大小列的总长度不得超过 字节有关组合索引中可变类型列的更多信息请参见注释部分 [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 中与索引一起存储 如果指定了 FILLFACTORSQL Server 会向上捨入每页要放置的行数例如发出 CREATE CLUSTERED INDEX FILLFACTOR = 将创建一个 FILLFACTOR 为 % 的聚集索引假设 SQL Server 计算出每页空间的 % 为 行SQL Server 将其向上捨入这样每页就放置 行 说明显式的 FILLFACTOR 设置只是在索引首次创建时应用SQL Server 并不会动态保持页上可用空间的指定百分比 用户指定的 FILLFACTOR 值可以从 到 如果没有指定值默认值为 如果 FILLFACTOR 设置为 则只填满叶级页可以通过执行 sp_configure 更改默认的 FILLFACTOR 设置 只有不会出现 INSERT 或 UPDATE 语句时（例如对只读表）才可以使用 FILLFACTOR 如果 FILLFACTOR 为 SQL Server 将创建叶级页 % 填满的索引如果在创建 FILLFACTOR 为 % 的索引之后执行 INSERT 或 UPDATE会对每次 INSERT 操作以及有可能每次 UPDATE 操作进行页拆分 如果 FILLFACTOR 值较小（ 除外）就会使 SQL Server 创建叶级页不完全填充的新索引例如如果已知某个表包含的数据只是该表最终要包含的数据的一小 |
