SQL Server 索引

聚集索引创建的时机：

1、明确的创建语句。

2、表中没有聚集索引，在创建主键的时候，会在主键所在的列（单一列、组合列）创建唯一索引。此时如果没有指定索引类型，则默认的索引类型为聚集索引即唯一聚集索引。

非聚集索引创建的时机：

1、明确的创建语句。

2、表中添加唯一约束的时候，会在约束的列上（单一列、组合列）创建唯一索引。此时如果没有指定索引类型，则默认的索引类型为非聚集索引即唯一非聚集索引。

在表中有聚集索引的时候，非聚集索引的叶子节点会存储聚集键。如果表没有聚集索引则通过RID查找。因此一般来说聚集键不应该过长，因为每建一个非聚集索引都会保存一份聚集键。故聚集键的改变会带来很多的副作用：聚集索引的重新组织、非聚集索引被修改

聚集索引会改变表的结构（逻辑上、链表结构），非聚集索引是另外存一份数据，不会改变表的结构。

非聚集索引如果能够全部覆盖查询的数据（查询覆盖）（创建索引是使用include）此时效率最高（代价小），否则会发生书签查找（代价大）。

select * from  sys.dm_db_index_physical_stats('db_id','object_id','index_id','partition_number',null);

dbcc ind('db_id/db_name','object_id/object_name',1);

sp_autostats table_name;  

通过聚集索引查找数据：

1、SELECT TOP 1000 [A]
      ,[B]
  FROM [zws].[dbo].[AB]
  where a=21111   -- a 为聚集索引所在列

2、select * from sys.dm_db_index_physical_stats(DB_ID('zws'),object_id('dbo.ab'),1,null,'detailed') -- 查看B树层数。

3、dbcc ind(zws,ab,1)    --查看索引的详细信息。

 dbcc page('zws',1,413,3)  --由根节点开始，根据 childpageid 和 索引所在列a的值开始下一层的查找。

 dbcc page('zws',1,18720,3) -- 中间层，根据 childpageid 和 索引所在列a的值开始下一层的查找。

  dbcc traceon(3604);  --叶子节点
  go
  dbcc page('zws',1,19076,3)  
  go

通过非聚集索引查找数据：步骤同上，不过在堆表中会发现叶子节点存的是RID以及哈希值（非聚集索引的哈希值）

create function convert_RIDs (@rid BINARY(8))
  returns varchar(30)
/******************************************


功能：将RID转回fileid:pageid:slotnumber


******************************************/
as 
begin
   return(
convert (varchar(5),
   convert(int,substring(@rid,6,1)
   +substring(@rid,5,1)))
+':'+
convert (varchar(10),
   convert(int,substring(@rid,4,1)
   +substring(@rid,3,1)
   +substring(@rid,2,1)
   +substring(@rid,1,1)))
+':'+
convert (varchar(5),
   convert(int,substring(@rid,8,1)
   +substring(@rid,7,1))))
end;
go

非聚集索引没有定义为唯一时，则中间级别也会带上聚集键。

如果删除一个聚集索引叶子节点的所有数据，  dbcc ind(zws,ab,1)将不会再显示该叶子节点。但是运行 dbcc page(zws,1,18369,3) 仍会显示该页面数据。但是只会有一条。且Record Type = GHOST_DATA_RECORD（虚影）。非聚集索引中则会将数据直接删除。