【SQL】索引使用

--唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）

--主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。

--聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。

--非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。

--PS：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。


--SQL SERVER中有多种索引类型。

--按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）”

--按数据唯一性区分：“唯一索引”，“非唯一索引”

--按键列个数区分：“单列索引”，“多列索引”。



--语法：
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ] 

--参数说明：
--CREATE INDEX命令创建索引各参数说明如下：

--UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。

--CLUSTERED：用于指定创建的索引为聚集索引。

--NONCLUSTERED：用于指定创建的索引为非聚集索引。

--index_name：用于指定所创建的索引的名称。

--table：用于指定创建索引的表的名称。

--view：用于指定创建索引的视图的名称。

--ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。

--Column：用于指定被索引的列。

--PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。

--FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。

--IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。

--DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

--STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。

--SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。

--ON filegroup：用于指定存放索引的文件组。

--常用命令：
Exec sp_helpindex App_Transaction_History   --查看某个表已经存在的索引 

Exec sp_rename 'PK_APP_TRANSACTION_HISTORY','APP_TRANSACTION_HISTORY_UniClu_Index'  --将索引名由'PK_APP_TRANSACTION_HISTORY' 改为'APP_TRANSACTION_HISTORY_UniClu_Index'

drop index App_Transaction_History.PK_APP_TRANSACTION_HISTORY  --删除App_Transaction_History表中的PK_APP_TRANSACTION_HISTORY索引

dbcc showcontig(App_Transaction_History,PK_APP_TRANSACTION_HISTORY) --检查App_Transaction_History表中索引PK_APP_TRANSACTION_HISTORY的碎片信息

dbcc indexdefrag(OA_FL_Test,App_Transaction_History,PK_APP_TRANSACTION_HISTORY)  --整理OA_FL_Test数据库中App_Transaction_History表的索引PK_APP_TRANSACTION_HISTORY上的碎片

update statistics App_Transaction_History  --更新App_Transaction_History表中的全部索引的统计信息


--举例：

-- 创建唯一非聚集索引
create unique nonclustered        --表示创建唯一非聚集索引
index UQ_NonClu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)

--创建聚集索引
create clustered index Clu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建非聚集索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建唯一索引
create unique index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on) 

--表bigdata创建一个名为idx_mobiel的非聚集索引，索引字段为mobiel
create index idx_mobiel
on bigdata(mobiel) 


--表bigdata创建一个名为idx_id的唯一聚集索引，索引字段为id
--要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子为40
create unique clustered index idx_id
on bigdata(id) 
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute

参考：

https://www.cnblogs.com/Brambling/p/6754993.html

https://www.cnblogs.com/knowledgesea/p/3672099.html