MS SQL SERVER 索引以及‘表中的列的类型不能用作索引中的键列’的问题

1.索引分类

MS SqlServer提供了两种索引：聚集索引和非聚集索引；

• 聚集索引是将数据按照索引的顺序存放
• 非聚集索引是将索引和数据分离存放，通过指针将二者联系到一起。

因为两种索引对比：

• 使用聚集索引查询效率更高，但对表的更新效率影响较大，因此适用于更新读写比较大的表，例如商品详情（创建后很少改动，查看的频率却相对较高）
• 使用非聚集索引则查询效率较低，对表的更新效率影响较小，适用于对查询需求不大打表，例如日志表

 

聚集索引注意点：

1. 聚集索引在一个表中只能有一个，但是不限于一列，可以是包含多个列的组合索引
2. 虽然可以多列，但是列越少越好
3. 创建聚集索引的列的重复性越低越好，因为如果聚集索引不是唯一值，SQL SERVER会生成额外的标识信息以区分数据
4. 适合使用在返回一个返回值的场合：BETWEEN...AND , >= , <= , > , <
5. 适合返回大型结果集的查询
6. 适合被连续访问的列
7. 适合经常被连接及Group By的列
8. 最好使用窄列或窄列组合作为聚集索引列
9. 不适合频繁更改的列（由于表是按照索引排序的，索引的更新将导致数据整行移动

非聚集索引注意点：

1. 非聚集索引可以看成是高效率查询数据的一种途径。例如对人群进行筛选可以从年龄，收入，性别等多种角度切入，每个角度都可以建立一个非聚集索引

 

创建聚集索引和非聚集索引的列是有要求的，以下类型无法创建索引：

• binary、varbinary
• ntext、text、image
• varchar(max)、nvarchar(max)
• uniqueidentifier
• rowversion、timestamp
• sql_variant
• 精度大于 18 位的 decimal 和 numeric
• 标量大于 2 的 datetimeoffset
• CLR 类型(hierarchyid和空间类型)
• xml

•  如果使用以上类型的列，会出现错误【表'TABLE_NAME'中的列 'COLUMN_NAME' 的类型不能用作索引中的键列。】

   

  使用索引的好处：能够根据查询要求，迅速缩小查询范围，避免全表扫描。

 

索引的创建和使用：

创建唯一索引要在前面加上unique

Create [unique] index index_name

On table_name (column[,…n])

index_name：索引名

table_name：表名

column：字段名

SQL中创建的语法：

Create [unique][clustered][nonclustered]index index_name

On {table|view} (column[asc|desc][,…n

 

可选参数的意思：unique  唯一的

                             clustered    聚集索引    一张表只能有一个聚集索引

                             nonclustered  非聚集索引    一张表可以有多个非聚集索引

删除索引：语法drop index tablename.indexname

Drop index 表名.索引名

索引可以加快查询的速度，数据量比较大的时候是很有用的，但是会减慢数据的插入更新速度。