【SQL Server学习笔记】索引

花花世界蕾

　　通过建立索引，能加快数据的访问速度。
　　每个表只能有一个聚集索引，可以考虑列有：用来排序大型结果集的列，用在聚合函数中的列，包含完整唯一值的列；而频繁更新的列，非唯一的列，非常多列(多个列的组合)，非常宽的列则不适合建聚集索引。
　　在选择非聚集索引的列时，一般是那些在where，join，order by等子句中频繁引用的那些列，以及搜索返回较小结果集的高选择性列（少于表中所有行的20%）。
　　此外，还有一些限制：
　　A、每个表最多可以有249个非聚集索引；
　　B、索引键列最多16个，总长不超过900字节，但include中可以包含1023个非键列；
CREATE TABLE t(v1 CHAR(300),                 v2 CHAR(300),                 v3 CHAR(300),                 v4 CHAR(300),                 v5 CHAR(200))                     CREATE INDEX>　　上面语句在建立索引时会报错：未创建索引 'idx_tt'。此索引有一个键的长度至少为 1200 字节。允许的最大键长度为 900 字节。 Severity 16。
　　C、在索引键中不能包含大数据类型，但可以把新的max型类型列加入到include中。
　　除了上面的限制之外，SQL Server中有主键约束、唯一约束、外键约束、默认值约束、Check约束，在建立索引时要特别注意的是：
　　A、在定义表时如果指定了主键（主键约束），那么系统后自动建一个聚集索引
　　B、在定义表时如果指定了唯一约束，那么系统会自动建立一个唯一的非聚集索引。
　　C、主键+聚集索引，主键+非聚集索引，唯一约束+聚集索引，唯一约束+非聚集索引，这几种组合都是可以的。注意：主键约束和唯一约束的区别是唯一约束中的列值可以包含一个NULL。
　　前者是约束，后者是索引方式，两者没有必然的联系，不是说主键就一定要用聚集索引，其实主键也可以用非聚集索引，因为前者只是强调每个值都要不同且不能为NULL，而后者则是影响了数据的存储方式，通过定义聚集索引，数据按照一个确定的方式存储在磁盘上，数据页通过双向链表串连起来，每个指针都指向一个确定的数据页，这和堆表不同，堆表的数据页不会通过指针前后串联起来，有点像二叉树，从根开始指向下一级，一级一级指下去，页与页之间没有联系。
--1.1建表，定义主键会自动建立聚集索引  CREATE TABLE tt(vid int primary key,                  v1 char(100),                 v2 varchar(100))    --1.2                 create table t2(vid int NOT NULL, --这里必须是not null                 v1 char(100),                 v2 varchar(100),                 v3 int not null)      --1.3通过增加主键约束建立聚集索引，同时指定索引选项 >　　索引压缩
--创建分区索引,同时指定指定页级压缩  create nonclustered index>　　修改索引选项：指定不重新生成或重新组织索引的索引选项。不能为已禁用的索引指定SET。
/*=========================================== >　　索引元数据
select      t.name ,     t.type_desc,          --是否由sql server内部组件创建的     t.is_ms_shipped,             --对象是否要发布     t.is_published,          --对象架构是否发布     t.is_schema_published,          --FILESTREAM文件组的空间id     t.filestream_data_space_id,          --text,ntext,image数据保存的空间id     t.lob_data_space_id,          --曾经使用的最大列id     t.max_column_id_used,          --禁用时大容量更新获取行锁,启用时会获取大容量更新锁     t.lock_on_bulk_load,     t.uses_ansi_nulls,          --是否使用快照复制或事务复制发布表     t.is_replicated,          --表有复制过滤器     t.has_replication_filter,          --使用合并复制发布表     t.is_merge_published,          --使用同步更新订阅来订阅表     t.is_sync_tran_subscribed,          --表包含的持久化数据,依赖上次ALTER ASSEMBLY期间其定义发生更改的程序集     t.has_unchecked_assembly_data,          --行中最大允许字节数,可以允许直接在行中存储LOB数据     t.text_in_row_limit,          --是否在行外存储varchar(max),nvarchar(max),varbinary(max),xml,UDT类型的数据     --为1时只在行内存储一个16字节的指针指向根目录     --为0时直接存储在行中，如果容纳不下，那么存储在行外     t.large_value_types_out_of_row,          --表是否启用了变更数据捕获     t.is_tracked_by_cdc,          --锁升级     t.lock_escalation_desc,          i.name,      i.type_desc,              --索引类型     i.is_unique,              --是否唯一索引     i.data_space_id,          --索引存储的空间id     i.ignore_dup_key,         --是否忽略重复值          i.is_primary_key,         --是否主键     i.is_unique_constraint,   --是否是唯一约束          i.fill_factor,            --填充因子     i.is_padded,              --中间层是否也按照填充因子来填充     i.is_disabled,            --是否禁用     i.is_hypothetical,        --是否假设索引     i.allow_row_locks,        --是否允许行级锁     i.allow_page_locks,       --是否允许页级锁     i.has_filter,             --是否由过滤条件     i.filter_definition,      --过滤条件的定义          ic.index_column_id,       --索引中列的id，在索引中时唯一的     ic.column_id,             --索引中列对应到表中的列id     ic.key_ordinal,           --索引中列的次序     ic.is_descending_key,     --是否降序排列     ic.is_included_column,    --是否是include中的列          c.name                    --列名称           from  sys.tables t  inner join sys.indexes i          on t.object_id = i.object_id  inner join sys.index_columns ic          on ic.object_id = i.object_id             and ic.index_id = i.index_id  inner join sys.columns c          on c.object_id = ic.object_id             and c.column_id = ic.column_id  where t.object_id = object_id('t2')         and i.index_id = 3    　　索引维护
--1.重建索引，如果重建的是聚集索引，那么整个表的数据都会重建 >