SQL server创建索引

使用T-SQL语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 

    INDEX   index_name

     ON table_name (column_name…)

      [WITH FILLFACTOR=x]

    UNIQUE表示唯一索引，可选

   CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选

     FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

术语定义

UNIQUE

在表上创建唯一索引。唯一索引是不允许其中任意两行具有相同索引值的索引。

SQL Server 2005 Compact Edition (SQL Server Compact Edition) 在创建索引后将检查是否存在重复的值（如果数据已存在），并在每次使用 INSERT 或 UPDATE 语句添加数据时执行该检查操作。必须先消除重复值，然后才可对列创建唯一索引。如果存在重复的键值，则将取消 CREATE INDEX 语句并返回错误。只能对定义为 NOT NULL 的列创建唯一索引。

如果存在唯一索引，则可能生成重复键值的 UPDATE 或 INSERT 语句将回滚，且 SQL Server Compact Edition 返回错误。即使 UPDATE 或 INSERT 语句更改许多行，但只要存在一个重复，上面这一点也将成立。

NONCLUSTERED

创建指定表的逻辑排序的索引。使用非聚集索引，数据行的物理顺序将独立于其索引顺序。这是唯一支持的索引类型。（默认值为 NONCLUSTERED）

index_name

指定索引的名称。索引名称在表中必须是唯一的，但是在数据库中不必是唯一的。

table_name

指定要对其创建索引的表的名称。

此表包含要建立索引的一个或多个列。

column name

要应用索引的列。指定两个或两个以上的列的名称，以对指定列中的组合值创建组合索引。在表后面的括号中，按排序优先级顺序列出要包含在组合索引中的列。

注意：不能将包含 ntext 或 image 数据类型的列指定为要建立索引的列。

 

ASC | DESC ]

为特定的索引列确定升序 (ASC) 或降序 (DSC) 排序方向。默认值为 ASC。

n

指示可以为任何特定索引指定多列的占位符。索引中可以包含的最大列数为 16。

STATISTICS_NORECOMPUTE

指定是否重新计算分发统计信息。默认值为 OFF。

• ON 
  不自动重新计算过期的统计信息。
• OFF 
  启用自动统计信息更新

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

重要事项：禁用分发统计信息的自动重新计算功能可能会阻止查询优化器为涉及此表的查询选取最佳执行计划。

 

 总结：
      1.什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
  2.分类：
     唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
     主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
     聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
     非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引
    3.创建索引的标准：用语频繁搜索的列；用语对数据进行排序的列
注意：如果表中仅有几行，或列中只包含几个不同的值，不推荐创建索引，因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。

主键（PRIMARY KEY）与聚集索引（CLUSTER INDEX）
严格来说，主键与聚集索引没有任何关系，如果要说有话，那就是表中没有聚集索引的时候，创建的主键默认就是聚集索引（除非有特别设置为NOCLUSTER）。
在主键与聚集索引的处理方面，注意以下事项：
1、主键不与聚集索引分离
2、聚集索引键列尽量避免使用int之外的数据类型
3、尽量避免使用复合主键


创建索引时的注意事项

1、始终包含聚集索引
当表中不包含聚集索引时，表中的数据是无序的，这会降低数据检索效率。即使通过索引缩小了数据检索的范围，但由于数据本身是无序的，当从表中提取实际数据时，会产生频繁的定位问题，这也使得SQL Server基本上不会使用无聚集索引表中的索引来检索数据。
2、保证聚集索引唯一
由于聚集索引是非聚集索引的行定位器，如果它不唯一，则会使行定位器中包含辅助数据，同时也导致从表中提取数据时，需要借助行定位器中的辅助数据来定位，这会降低处理效率。
3、保证聚集索引最小
每个聚集键值都是所有非聚集索引的叶结点记录，它越小，意味着每个非聚集索引的索引叶包含的有效数据越多，这对于提升索引效率很有好处。
4、覆盖索引
覆盖索引是指索引中的列包含了数据处理中涉及的所有列，覆盖索引相当原始表的一个子集，由于这个子集中包含了数据处理涉及的所有列，因此操作这个子集就可以满足数据处理需要。一般而言，如果大多数处理都只涉及某个大表的某些列，可以考虑为这些列建立覆盖索引。
覆盖索引的建立方法是将要包含的列中的关键列做为索引键列，将其他列做为索引的包含列（使用索引创建语句中的INCLUDE子句）。
5、适量的索引
当数据发生变化时，SQL Server会同步维护相关索引中的数据，过多的索引会加影响数据变更的处理效率。因此，只应该在经常使用的列上建立索引。
适量的索引还体现在对索引列的组合方式的控制上。例如，如果有两个列col1和col2，这两个列的组合会产生三种使用情况：单独使用col1、单独使用col2及同时使用col1和col2。如果有为每种情况都建立索引，则需要建立三个索引。但也可以只建立一个复合索引（col1, col2），这样能够依次满足col1+col2、col1、col2这三种方式的查询，其中，col2利用这个查询会比较勉强（还要配合单独的统计），可以视实际情况确定是否需要为col2建立单独的索引。
特别注意：
不要建立重复索引，目前最常见的重复索引是单独为某个列建立主键和聚集索引
与直接从表中提取数据相比，根据索引检索数据，多了一个索引检索的过程，这个过程要求能够尽量缩小数据检索范围，并且使用最少的时间，这样才能真正保证能够通过索引提高数据检索效率。
实现上述目的，对于索引键列的选择，应该遵循如下原则：
选择性原则
选择性是满足条件的记录占总记录数的百分比，这个比率应该尽可能低，这样才能保证通过索引扫描后，只需要从基础表提取很少的数据。
如果这个比率偏高，则不应该考虑在此列上建立索引。
数据密度原则
数据密度是指列值唯一的记录占总记录数的百分比，这个比率越高，则说明此列越适合建立索引。
在考虑数据密度的时候，还要注意数据分布的问题，只有经常检索的密度高时，才适合建立索引。例如，如果一张表有10万记录，虽然某个列不重复的记录有9万条，但如果经常检索的2万条记录，其不重复的列值才几十条的话，这个列是不太适合建立索引的。另一种情况是，整体数据密度不大，但经常检索的数据的密度大，例如订单的状态，一般来说，订单的状态就几种，但已经Close的订单往往占整个数据的绝大部分，但数据处理的时候，基本上都是检索未Close的订单，这种情况下，为订单的状态列建立索引还是比较有效的（SQL Server 2008中，可以为这种列建立具有更佳效果的筛选索引）。
6、索引键列大小
一般不宜为超过100Byte的列建立索引。
7、复合索引键列顺序
在索引中，索引的顺序主要由索引中的每一个键列确定，因此，对于复合索引，索引中的列顺序是很重要的，应该优先把数据密度大，选择性列，存储空间小的列放在索引键列的前面。