8.索引的简单介绍

--索引是一个单独存储在磁盘上，对数据库表中一列或者多列的值迚行排序的一种数据库结构。索引包含着对数据表里所有记录的引用指针
--分类：
--聚集索引 clustered index
--非聚集索引 nonclustered index
--聚集索引示意图

--非聚集索引示意图

--聚集索引
--聚集索引的索引页面直接指向数据页面，所以查找速度快，但每张表只能创建一个聚集索引，
--聚集索引需要至少相对于该表120%的附件空间，以存放该表的副本和索引中间页
--非聚集索引
--每个索引中丌包含记录的数据，是数据行的指针。每个表可以有多个非聚集索引。
--其他索引
--唯一索引 确保索引键不包含重复的键。
--包含列索引 非聚集索引，不仅包含键列，还包含非键列
--索引视图        结果集保留在数据库中，并建立了索引以供快速访问的视图
--全文索引        基于标记的索引，通过sqlserver的全文引擎服务创建，使用和维护，目的是为了用户提供在字符串数据中高效搜索复杂词语
--空间索引        针对geometry数据类型的索引
--Xml        对xml数据类型的字段创建主索引，对xml数据的元素名，值，属性和路径进行创建索引

--索引的设计原则
--（1）选择唯一性索引
--（2）为经常需要排序、分组和联合操作的字段建立索引
--（3）为常作为查询条件的字段建立索引
--（4）限制索引的数目
--（5）尽量使用数据量少的索引

--创建索引
--语法
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | 
DESC ] [ ,...n ] )
[INCLUDE (column_name [ ,...n ])]
[with
[PAD_INDEX={ON|OFF}]
[[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY={ON|OFF}]
[[,]DROP_EXISTING={ON|OFF}]
[[,]STATISTICS_NORECOMPUTE={ON|OFF}]
[[,]SORT_IN_TEMPDB={ON|OFF}]
]
[ ON filegroup ]
--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：用于指定存放索引的文件组。

--创建非聚集索引(为student表sname创建索引)
create nonclustered index index_student on student (sname asc)
--with(drop_existing=on)--如果已经存在同名索引则删除，若无同名就不用了加
go
--用系统存储过程查看索引信息
exec sp_helpindex[@objectname=] 'name'
--修改索引
--重新生成索引
alter index index_student on studnet rebuild
go
--重新组织索引
alter index index_student on studnet reorganize
go
--禁用索引
alter index index_student on student disable
go
--删除索引
drop index student.index_student

--无论何时对基础数据执行揑入、更新或删除操作，SQL server数据库引擎都会自劢维护索引。
--随着时间的推移，这些修改可能会导致 中的信息分散在数据库中（含有碎片）。
-- 可以通过重新组织或重新生成索引来修复索引碎片。
--维护索引的用户必须是sysadmin固定服务器角色的成员，或者是db_ddladmin和db_owner固定数据库角色的成员

--使用sql检查碎片
select a.index_id,name,avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(N'databasename')),object_id(N'tablename')
as a join sys.indexses as b
on a.object_id=b.object_id and a.index_id=b.index_id

--重新生成碎片
alter index index_id on tablename rebuild
partition=all
with(pad_index=off,
statistics_norecompute=on,
allow_row_locks=on,
allow_page_locks=on,
ignore_dup_key=off,
online=off,
sort_in_tempdb=off
)

--重新生成表中的所有索引
alter index all on tablename
rebuild with(fillfactor=80,sort_in_tempdb=on,statistics_norecompute=on)
go