SQLServer之添加聚集索引

聚集索引添加规则

聚集索引按下列方式实现

• primary key 和 unique 约束

  在创建 primary key 约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。 主键列不允许空值。

  在创建 unique 约束时，默认情况下将创建唯一非聚集索引，以便强制 unique 约束。 如果不存在该表的聚集索引，则可以指定唯一聚集索引。

  将索引创建为约束的一部分后，会自动将索引命名为与约束名称相同的名称。 有关详细信息，请参阅 primary and foreign key constraints 和 unique constraints and check constraints。

• 独立于约束的索引

  指定非聚集主键约束后，您可以对非主键列的列创建聚集索引。

限制和局限

• 创建聚集索引结构后，旧（源）结构和新（目标）结构的各自的文件和文件组都需要磁盘空间。 在完成事务提交后，才会释放旧结构。 排序也需要其他临时磁盘空间。 有关详细信息，请参阅 disk space requirements for index ddl operations。

• 如果对具有多个现有非聚集索引的堆创建聚集索引，则必须重新生成所有非聚集索引，以使它们包含聚集键值而非行标识符 (rid)。 同样，如果删除具有多个非聚集索引的表的聚集索引，在 drop 操作过程中，将重新生成非聚集索引。 对于大型表，这可能需要很长时间。

  对大型表创建索引的首选方法是先创建聚集索引，然后创建任何非聚集索引。 在对现有表创建索引时，请考虑将 online 选项设置为 on。 如果设置为 on，则不会持有长期表锁。 这使对基础表的查询或更新可以继续进行。 有关详细信息，请参阅 perform index operations online。

• 聚集索引的索引键不能包含在 row_overflow_data 分配单元中具有现有数据的 varchar 列。 如果对 varchar 列创建了聚集索引，并且在 in_row_data 分配单元中存在现有数据，则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。 若要获得有关可能包含行溢出数据的表的信息，请使用 sys.dm_db_index_physical_stats (transact-sql) 动态管理函数。

使用ssms数据库管理工具添加聚集索引

使用表设计器创建聚集索引

1、连接数据库，选择数据库，选择数据表-》右键点击-》选择设计。

2、在表设计窗口-》选择要添加索引的数据列-》右键点击-》选择索引/键。

3、在索引/键弹出框中-》点击添加-》在常规窗口中类型选择索引-》点击选择列。

4、在索引列弹出框中-》选择索引列-》选择排序方式-》可以把索引建在一列或者多列上-》点击确定。

5、在索引/键弹出框中-》输入索引名称-》输入索引描述-》其它设置可以选择系统默认也可以选择自己设置-》点击关闭。

6、点击保存(或者ctrl+s)->关闭表设计器-》刷新表-》查看结果。

使用对象资源管理器创建聚集索引

1、连接数据库，选择数据库-》展开数据表-》右键单击索引文件夹-》选择新建索引-》选择聚集索引。

2、在新建索引弹出框-》在索性名称中输入索引名称-》选择是否创建唯一聚集索引-》点击添加，添加要添加索引的数据列。

3、在数据表弹出框中-》选择数据列，可以选择多个-》点击确定。

4、在新建索引弹出框中-》点击选项，关于选项配置可以选择系统默认，也可以根据需要自己配置。

5、在新建索引弹出框中-》点击存储，可以选择默认文件组和分区方案，也可以自行配置。

6、在新建索引弹出框中-》点击扩展属性，点击文件编辑符，输入索引描述-》单击确定。

7、在对象资源管理器中即可看到新创建的索引(如果没有出现可以刷新再次查看)。

使用t-sql脚本添加聚集索引

语法：

--声明数据库引用
use 数据库名;
go

--判断索引是否存在
if exists(select * from sysindexes where name=索引名)
drop index 索引名 on 表名 with (online=off);
go

--添加索引
create
--[unique] --指定聚集索引是否唯一
[clustered | nonclustered]  --指定为聚集索引
index 索引名称 --索引名称
on 表名  --索引添加在哪个表
(列名 [asc | desc],列名 [asc | desc]) --索引添加在哪个数据列
with(

 

--pad_index:指定索引填充
--pad_index=on:fillfactor 指定的可用空间百分比应用于索引的中间级页。
--pad_index=off或未指定 fillfactor:考虑到中间级页上的键集，可以将中间级页几乎填满，但至少要为最大索引行留出足够空间。
pad_index={ on | off },

 

--statistics_norecompute:指定是否重新计算统计信息。
--statistics_norecompute=on:过时的统计信息不会自动重新计算。
--statistics_norecompute=off:启用自动统计信息更新。
statistics_norecompute={ on | off },

 

--sort_in_tempdb:指定是否将排序结果存储在 tempdb 中。
--sort_in_tempdb=on:在tempdb中存储用于生成索引的中间排序结果。如果tempdb与用户数据库不在同一组磁盘上，就可缩短创建索引所需的时间。但是，这会增加索引生成期间所使用的磁盘空间量。
--sort_in_tempdb=off:中间排序结果与索引存储在同一数据库中。
sort_in_tempdb={ on | off },

 

--ignore_dup_key:指定在插入操作尝试向唯一索引插入重复键值时的响应类型。 ignore_dup_key 选项仅适用于创建或重新生成索引后发生的插入操作。 当执行 create index、alter index 或 update 时，该选项无效。 默认为 off。
--ignore_dup_key=on:打开，将重复键值插入唯一索引时会出现警告消息。只有违反唯一性的行为才会失败。
--ignore_dup_key=off:关闭,将重复键值插入唯一索引时会出现错误消息。回滚整个insert操作。对于对视图创建的索引、非唯一索引、xml 索引、空间索引以及筛选的索引，ignore_dup_key 不能设置为 on
ignore_dup_key={ on | off },

 

--drop_existing：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 off。
--drop_existing=on:指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。
--drop_existing=off:指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，sql server 将显示一个错误。
drop_existing={ on | off },

 

--online:指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认为 off。 rebuild 可作为 online 操作执行。
--online=on:在索引操作期间不持有长期表锁。 在索引操作的主要阶段，源表上只使用意向共享 (is) 锁。
--这使得能够继续对基础表和索引进行查询或更新。
--操作开始时，在很短的时间内对源对象持有共享 (s) 锁。
--操作结束时，如果创建非聚集索引，将在短期内获取对源的 s（共享）锁；
--当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 sch-m（架构修改）锁。 但联机索引锁是短的元数据锁，特别是 sch-m 锁必须等待此表上的所有阻塞事务完成。
--在等待期间，sch-m 锁在访问同一表时阻止在此锁后等待的所有其他事务。 对本地临时表创建索引时，online 不能设置为 on。
--online=off:在索引操作期间应用表锁。这样可以防止所有用户在操作期间访问基础表。
--创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (sch-m) 锁。
--这样可以防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引操作将对表获取共享 (s) 锁。 这样可以防止更新基础表，但允许读操作（如 select 语句）。
online={ on | off },

 

--aloow_row_locks:指定是否允许行锁。
--allow_row_locks=on:访问索引时允许行锁。数据库引擎确定何时使用行锁。
--allow_row_locks=off:不使用行锁。
allow_row_locks={ on | off },

 

--allow_page_locks:指定是否允许使用页锁。
--allow_page_locks=on:访问索引时允许页锁。数据库引擎确定何时使用页锁。
-- allow_page_locks=off:不使用页锁。
allow_page_locks={ on | off },

 

--fillfactor=n：指定一个百分比，指示在数据库引擎创建或修改索引的过程中，应将每个索引页面的叶级填充到什么程度。 指定的值必须是 1 到 100 之间的整数。 默认值为 0。
fillfactor=n

 

--maxdop=max_degree_of_parallelism:在索引操作期间替代 max degree of parallelism 配置选项。 有关详细信息，请参阅 配置 max degree of parallelism 服务器配置选项。 使用 maxdop 可以限制在执行并行计划的过程中使用的处理器数量。 最大数量为 64 个处理器。
--max_degree_of_parallelism 可以是：
--1 - 取消生成并行计划。
-->1 - 将并行索引操作中使用的最大处理器数量限制为指定数量。
--0（默认值）- 根据当前系统工作负荷使用实际数量的处理器或更少数量的处理器。
--有关详细信息，请参阅 配置并行索引操作。
--maxdop=max_degree_of_parallelism,

 

--data_compression=row:为指定的表、分区号或分区范围指定数据压缩选项。 选项如下所示：
--none
--不压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--row
--使用行压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--page
--使用页压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--columnstore
--适用范围： sql server 2014 (12.x) 到 sql server 2017。
--仅适用于列存储表。 columnstore 指定对使用 columnstore_archive 选项压缩的分区进行解压缩。 还原数据时，将继续通过用于所有列存储表的列存储压缩对 columnstore 索引进行压缩。
--columnstore_archive
--适用范围： sql server 2014 (12.x) 到 sql server 2017。
--仅适用于列存储表，这是使用聚集列存储索引存储的表。 columnstore_archive 会进一步将指定分区压缩到更小。 这可用于存档，或者用于要求更少存储并且可以付出更多时间来进行存储和检索的其他情形
--data_compression={ none | row | page | columnstore | columnstore_archive }

 

--on partitions ( { <partition_number_expression> | <range> } [ ,...n ] ) 适用范围： sql server 2008 到 sql server 2017。
--指定对其应用 data_compression 设置的分区。 如果表未分区，on partitions 参数将生成错误。 如果不提供 on partitions 子句，data_compression 选项将应用于已分区表的所有分区。
--可以按以下方式指定 <partition_number_expression>：
--提供一个分区号，例如：on partitions (2)。
--提供若干单独分区的分区号并用逗号将它们隔开，例如：on partitions (1, 5)。
--同时提供范围和单个分区，例如：on partitions (2, 4, 6 to 8)。
--<range> 可以指定为以单词 to 隔开的分区号，例如：on partitions (6 to 8)。
--，请多次指定 data_compression 选项
--on partitions(1-2)

)
on [primary];--数据空间规范
go

--添加注释
execute sp_addextendedproperty n'ms_description',n'索引说明',n'schema',n'dbo',n'table',n'test1',n'index',n'clus1';
go

示例：

--声明数据库引用
use testss;
go

--判断索引是否存在
if exists(select * from sysindexes where name='clus1')
drop index clus1 on test1 with (online=off);
go

--添加索引
create
--[unique] --指定聚集索引是否唯一
clustered --指定为聚集索引
index clus1 --索引名称
on test1 --索引添加在哪个表
(name asc,sex desc) --索引添加在哪个数据列
with(

--pad_index:指定索引填充
--pad_index=on:fillfactor 指定的可用空间百分比应用于索引的中间级页。
--pad_index=off或未指定 fillfactor:考虑到中间级页上的键集，可以将中间级页几乎填满，但至少要为最大索引行留出足够空间。
pad_index=off,

--statistics_norecompute:指定是否重新计算统计信息。
--statistics_norecompute=on:过时的统计信息不会自动重新计算。
--statistics_norecompute=off:启用自动统计信息更新。
statistics_norecompute=off,

--sort_in_tempdb:指定是否将排序结果存储在 tempdb 中。
--sort_in_tempdb=on:在tempdb中存储用于生成索引的中间排序结果。如果tempdb与用户数据库不在同一组磁盘上，就可缩短创建索引所需的时间。但是，这会增加索引生成期间所使用的磁盘空间量。
--sort_in_tempdb=off:中间排序结果与索引存储在同一数据库中。
sort_in_tempdb=off,

--ignore_dup_key:指定在插入操作尝试向唯一索引插入重复键值时的响应类型。 ignore_dup_key 选项仅适用于创建或重新生成索引后发生的插入操作。 当执行 create index、alter index 或 update 时，该选项无效。 默认为 off。
--ignore_dup_key=on:打开，将重复键值插入唯一索引时会出现警告消息。只有违反唯一性的行为才会失败。
--ignore_dup_key=off:关闭,将重复键值插入唯一索引时会出现错误消息。回滚整个insert操作。对于对视图创建的索引、非唯一索引、xml 索引、空间索引以及筛选的索引，ignore_dup_key 不能设置为 on
ignore_dup_key=off,

--drop_existing：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 off。
--drop_existing=on:指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。
--drop_existing=off:指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，sql server 将显示一个错误。
drop_existing=off,

--online:指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认为 off。 rebuild 可作为 online 操作执行。
--online=on:在索引操作期间不持有长期表锁。 在索引操作的主要阶段，源表上只使用意向共享 (is) 锁。
--这使得能够继续对基础表和索引进行查询或更新。
--操作开始时，在很短的时间内对源对象持有共享 (s) 锁。
--操作结束时，如果创建非聚集索引，将在短期内获取对源的 s（共享）锁；
--当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 sch-m（架构修改）锁。 但联机索引锁是短的元数据锁，特别是 sch-m 锁必须等待此表上的所有阻塞事务完成。
--在等待期间，sch-m 锁在访问同一表时阻止在此锁后等待的所有其他事务。 对本地临时表创建索引时，online 不能设置为 on。
--online=off:在索引操作期间应用表锁。这样可以防止所有用户在操作期间访问基础表。
--创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (sch-m) 锁。
--这样可以防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引操作将对表获取共享 (s) 锁。 这样可以防止更新基础表，但允许读操作（如 select 语句）。
online=off,

--aloow_row_locks:指定是否允许行锁。
--allow_row_locks=on:访问索引时允许行锁。数据库引擎确定何时使用行锁。
--allow_row_locks=off:不使用行锁。
allow_row_locks=on,

--allow_page_locks:指定是否允许使用页锁。
--allow_page_locks=on:访问索引时允许页锁。数据库引擎确定何时使用页锁。
-- allow_page_locks=off:不使用页锁。
allow_page_locks=on,

--fillfactor=n：指定一个百分比，指示在数据库引擎创建或修改索引的过程中，应将每个索引页面的叶级填充到什么程度。 指定的值必须是 1 到 100 之间的整数。 默认值为 0。
fillfactor=1

--maxdop=max_degree_of_parallelism:在索引操作期间替代 max degree of parallelism 配置选项。 有关详细信息，请参阅 配置 max degree of parallelism 服务器配置选项。 使用 maxdop 可以限制在执行并行计划的过程中使用的处理器数量。 最大数量为 64 个处理器。
--max_degree_of_parallelism 可以是：
--1 - 取消生成并行计划。
-->1 - 将并行索引操作中使用的最大处理器数量限制为指定数量。
--0（默认值）- 根据当前系统工作负荷使用实际数量的处理器或更少数量的处理器。
--有关详细信息，请参阅 配置并行索引操作。
--maxdop=1,

--data_compression=row:为指定的表、分区号或分区范围指定数据压缩选项。 选项如下所示：
--none
--不压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--row
--使用行压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--page
--使用页压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
--columnstore
--适用范围： sql server 2014 (12.x) 到 sql server 2017。
--仅适用于列存储表。 columnstore 指定对使用 columnstore_archive 选项压缩的分区进行解压缩。 还原数据时，将继续通过用于所有列存储表的列存储压缩对 columnstore 索引进行压缩。
--columnstore_archive
--适用范围： sql server 2014 (12.x) 到 sql server 2017。
--仅适用于列存储表，这是使用聚集列存储索引存储的表。 columnstore_archive 会进一步将指定分区压缩到更小。 这可用于存档，或者用于要求更少存储并且可以付出更多时间来进行存储和检索的其他情形
--data_compression=row

--on partitions ( { <partition_number_expression> | <range> } [ ,...n ] ) 适用范围： sql server 2008 到 sql server 2017。
--指定对其应用 data_compression 设置的分区。 如果表未分区，on partitions 参数将生成错误。 如果不提供 on partitions 子句，data_compression 选项将应用于已分区表的所有分区。
--可以按以下方式指定 <partition_number_expression>：
--提供一个分区号，例如：on partitions (2)。
--提供若干单独分区的分区号并用逗号将它们隔开，例如：on partitions (1, 5)。
--同时提供范围和单个分区，例如：on partitions (2, 4, 6 to 8)。
--<range> 可以指定为以单词 to 隔开的分区号，例如：on partitions (6 to 8)。
--，请多次指定 data_compression 选项
--on partitions(1-2)

)
on [primary];--数据空间规范
go

--添加注释
execute sp_addextendedproperty n'ms_description',n'第一个聚集索引',n'schema',n'dbo',n'table',n'test1',n'index',n'clus1';
go

 

聚集索引优缺点

优点：

1、表记录的排列顺序与索引的排列顺序一致，查询速度快。

2、对于那些经常要搜索范围值的列添加聚集索引执行效率更高。

3、对从表中检索的数据进行排序时经常要用到某一列，可在该表的该列上创建聚集（物理排序）索引，避免每次查询该列时都进行排序，节省成本。

缺点：

1、对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

2、索引需要占物理空间。

3、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。