精华内容
下载资源
问答
  • 创建唯一聚集索引典型实现 唯一索引可通过以下方式实现: PRIMARY KEY 或 UNIQUE 约束 在创建 PRIMARY KEY 约束时,如果不存在该表的聚集索引且未指定唯一非聚集索引,则将自动对一列或多列创建唯一聚集索引。 主键...

    创建唯一聚集索引典型实现

    唯一索引可通过以下方式实现:

    PRIMARY KEY 或 UNIQUE 约束

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

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

    有关详细信息,请参阅 Unique Constraints and Check Constraints 和 Primary and Foreign Key Constraints。

    独立于约束的索引

    可以为一个表定义多个唯一非聚集索引。

    有关详细信息,请参阅 CREATE INDEX (Transact-SQL)。

    索引视图

    若要创建索引视图,请对一个或多个视图列定义唯一聚集索引。 视图将执行,并且结果集存储在该索引的页级别中,其存储方式与表数据存储在聚集索引中的方式相同。 有关详细信息,请参阅 创建索引视图。

    创建唯一聚集索引限制和局限

    如果数据中存在重复的键值,则不能创建唯一索引、UNIQUE 约束或 PRIMARY KEY 约束。

    唯一非聚集索引可以包括包含性非键列。 有关详细信息,请参阅 Create Indexes with Included Columns。

    使用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> | } [ ,...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)。

    -- 可以指定为以单词 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'表名',N'index',N'索引名称';

    go

    示例:

    --声明数据库应用

    use testss;

    go

    --判断是否存在索引

    if exists(select * from sysindexes where name='uniqueclus1')

    drop index uniqueclus1 on test1 with (online=off);

    go

    create

    unique --唯一

    clustered --聚集索引

    index --索引关键字

    uniqueclus1 --索引名称

    on test1 --索引建立在哪张表

    (name asc) --索引建立在哪个数据列

    with(

    --pad_index:指定索引填充

    --pad_index=on:FILLFACTOR 指定的可用空间百分比应用于索引的中间级页。

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

    pad_index=on,

    --statistics_norecompute:指定是否重新计算统计信息。

    --statistics_norecompute=on:过时的统计信息不会自动重新计算。

    --statistics_norecompute=off:启用自动统计信息更新。

    statistics_norecompute=on,

    --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,

    --allow_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=none

    --on partitions ( { <partition_number_expression> | } [ ,...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)。

    -- 可以指定为以单词 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'uniqueclus1';

    go

    创建唯一索引优缺点

    优点:

    1、多列唯一索引能够保证索引键中值的每个组合都是唯一的。

    2、只要每个列中的数据是唯一的,就可以为同一个表创建一个唯一聚集索引。

    3、唯一索引能够确保定义的列的数据完整性。

    4、唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。

    5、创建唯一索引只会创建一个唯一索引,不会创建约束。

    缺点:

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

    2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    展开全文
  • Sql Server 创建唯一聚集索引典型实现

    千次阅读 2020-03-10 14:41:55
    创建唯一聚集索引典型实现 唯一索引可通过以下方式实现: PRIMARY KEY 或 UNIQUE 约束 在创建 PRIMARY KEY 约束时,如果不存在该表的聚集索引且未指定唯一非聚集索引,则将自动对一列或多列创建唯一聚集索引。...

    创建唯一聚集索引典型实现

    唯一索引可通过以下方式实现:

    • PRIMARY KEY 或 UNIQUE 约束

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

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

      有关详细信息,请参阅 Unique Constraints and Check Constraints 和 Primary and Foreign Key Constraints

    • 独立于约束的索引

      可以为一个表定义多个唯一非聚集索引。

      有关详细信息,请参阅 CREATE INDEX (Transact-SQL)

    • 索引视图

      若要创建索引视图,请对一个或多个视图列定义唯一聚集索引。 视图将执行,并且结果集存储在该索引的页级别中,其存储方式与表数据存储在聚集索引中的方式相同。 有关详细信息,请参阅 创建索引视图

    创建唯一聚集索引限制和局限

    • 如果数据中存在重复的键值,则不能创建唯一索引、UNIQUE 约束或 PRIMARY KEY 约束。

    • 唯一非聚集索引可以包括包含性非键列。 有关详细信息,请参阅 Create Indexes with Included Columns

    使用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'表名',N'index',N'索引名称';
    go

    示例:

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

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

    create 
    unique --唯一
    clustered --聚集索引 
    index --索引关键字
    uniqueclus1 --索引名称
    on test1 --索引建立在哪张表
    (name asc) --索引建立在哪个数据列
    with(

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

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

    --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,

    --allow_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=none

    --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'uniqueclus1';
    go

     

    创建唯一索引优缺点

    优点:

    1、多列唯一索引能够保证索引键中值的每个组合都是唯一的。 

    2、只要每个列中的数据是唯一的,就可以为同一个表创建一个唯一聚集索引。

    3、唯一索引能够确保定义的列的数据完整性。

    4、唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。

    5、创建唯一索引只会创建一个唯一索引,不会创建约束。

    缺点:

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

    2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    展开全文
  • 在创建 PRIMARY KEY 约束时,如果不存在该表的聚集索引且未指定唯一非聚集索引,则将自动对一列或多列创建唯一聚集索引。 主键列不允许空值。 在创建 UNIQUE 约束时,默认情况下将创建唯一非聚集索引,以便强制 ...

    创建唯一非聚集索引典型实现

    唯一索引可通过以下方式实现:

    PRIMARY KEY 或 UNIQUE 约束

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

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

    有关详细信息,请参阅 Unique Constraints and Check Constraints 和 Primary and Foreign Key Constraints。

    独立于约束的索引

    可以为一个表定义多个唯一非聚集索引。

    有关详细信息,请参阅 CREATE INDEX (Transact-SQL)。

    索引视图

    若要创建索引视图,请对一个或多个视图列定义唯一聚集索引。 视图将执行,并且结果集存储在该索引的页级别中,其存储方式与表数据存储在聚集索引中的方式相同。 有关详细信息,请参阅 创建索引视图。

    创建唯一非聚集索引限制和局限

    如果数据中存在重复的键值,则不能创建唯一索引、UNIQUE 约束或 PRIMARY KEY 约束。

    唯一非聚集索引可以包括包含性非键列。 有关详细信息,请参阅 Create Indexes with Included Columns。

    使用SSMS数据库管理工具创建唯一非聚集索引

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

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

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

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

    4、在索引列弹出框-》点击添加索引列-》点击选择索引列排序规则-》可以把一个索引建立在多个数据列上-》点击确定。

    5、在索引/键弹出框-》选择创建唯一索引-》输入索引名称-》输入索引描述-》选择创建为非聚集索引-》其它可以选择默认-》点击关闭。

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

    使用对象资源管理器创建唯一索引

    1、连接数据库,选择数据库,选择数据表-》展开对象资源管理器-》右键点击索引-》选择新建索引-》选择非聚集索引。

    2、在新建索引弹出框-》输入索引名称-》选择创建为唯一索引-》点击添加,选择索引数据列。

    3、在数据表列-》选择索引要添加的数据列,可以选择多个-》点击确定。

    4、在新建索引窗口-》点击包含性列-》点击添加。

    5、在数据表列-》点击选择包含性列,可以选择多个-》点击确定。

    6、在新建索引窗口-》点击选项-》可以自己设置索引属性。

    7、在新建索引弹出框-》点击存储-》设置索引和数据存储规则。

    8、在新建索引弹出框-》点击筛选器-》输入筛选规则。

    9、在新建索引弹出框-》点击扩展属性-》输入扩展属性名称-》输入扩展属性名称-》可以输入多个扩展属性-》点击确定。

    10、查看创建结果。

    使用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],...) --索引添加在哪个数据列

    --可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列,可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点:

    --1、它们可以是不允许作为索引键列的数据类型。

    --2、在计算索引键列数或索引键大小时,数据库引擎不考虑它们。

    --当查询中的所有列都作为键列或非键列包含在索引中时,带有包含性非键列的索引可以显著提高查询性能。

    --这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值;不访问表或聚集索引数据,从而减少磁盘 I/O 操作。

    include(列名,......)

    where (筛选表达式) --筛选表达式

    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> | } [ ,...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)。

    -- 可以指定为以单词 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'表名',N'index',N'索引名称';

    go

    示例:

    --声明数据库应用

    use testss;

    go

    --判断是否存在索引

    if exists(select * from sysindexes where name='uniquenonclus1')

    drop index uniquenonclus1 on test1 with(online=off);

    go

    create

    unique --唯一

    nonclustered --非聚集索引

    index --索引关键字

    uniquenonclus1 --索引名称

    on test1 --索引建立在哪张表

    (name asc) --索引建立在哪个数据列

    --可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列,可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点:

    --1、它们可以是不允许作为索引键列的数据类型。

    --2、在计算索引键列数或索引键大小时,数据库引擎不考虑它们。

    --当查询中的所有列都作为键列或非键列包含在索引中时,带有包含性非键列的索引可以显著提高查询性能。

    --这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值;不访问表或聚集索引数据,从而减少磁盘 I/O 操作。

    include(sex)

    where (name is null) --筛选表达式

    with(

    --pad_index:指定索引填充

    --pad_index=on:FILLFACTOR 指定的可用空间百分比应用于索引的中间级页。

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

    pad_index=on,

    --statistics_norecompute:指定是否重新计算统计信息。

    --statistics_norecompute=on:过时的统计信息不会自动重新计算。

    --statistics_norecompute=off:启用自动统计信息更新 。 statistics_norecompute=on,

    --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,

    --allow_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=none

    --on partitions ( { <partition_number_expression> | } [ ,...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)。

    -- 可以指定为以单词 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'uniquenonclus1';

    go

    创建唯一非聚集索引优缺点

    优点:

    1、只要每个列中的数据是唯一的,就可以为同一个表创建多个唯一非聚集索引。

    2、唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。

    3、唯一索引能够确保定义的列的数据完整性。

    4、多列唯一索引能够保证索引键中值的每个组合都是唯一的。

    5、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    6、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    缺点:

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

    2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。

    3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。

    展开全文
  • 在上篇文章里,我讨论了唯一和非唯一聚集索引的区别。我们已经知道,SQL Server内部使用4 bytes的...当我们在表上定义PRIMARY KEY约束时,SQL Server会为我们创建唯一聚集索引;另外我们可以通过CREATE UNIQUE CL...

    在上篇文章里,我讨论了唯一和非唯一聚集索引的区别。我们已经知道,SQL Server内部使用4 bytes的uniquifier来保证非唯一聚集索引行唯一。今天我们来看下唯一聚集索引上,唯一和非唯一非聚集索引的区别。当我们在表上定义PRIMARY KEY约束时,SQL Server会为我们创建唯一聚集索引;另外我们可以通过CREATE UNIQUE CLUSTERED INDEX语句在表上创建唯一聚集索引。下面的代码会创建customers表,然后在它上面创建唯一聚集索引,最后在表上创建唯一和非唯一非聚集索引。

    复制代码
     1 -- Create a table with 393 length + 7 bytes overhead = 400 bytes 
     2 -- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24 
     3 CREATE TABLE Customers 
     4 ( 
     5     CustomerID INT NOT NULL, 
     6     CustomerName CHAR(100) NOT NULL, 
     7     CustomerAddress CHAR(100) NOT NULL, 
     8     Comments CHAR(189) NOT NULL 
     9 ) 
    10 GO
    11   
    12 -- Create a unique clustered index on the previous created table 
    13 CREATE UNIQUE CLUSTERED INDEX idx_Customers ON Customers(CustomerID) 
    14 GO  
    15 -- Insert 80.000 records 
    16 DECLARE @i INT = 1 
    17 WHILE (@i <= 80000) 
    18 BEGIN 
    19     INSERT INTO Customers VALUES 
    20     ( 
    21         @i, 
    22         'CustomerName' + CAST(@i AS CHAR), 
    23         'CustomerAddress' + CAST(@i AS CHAR), 
    24         'Comments' + CAST(@i AS CHAR) 
    25     )     
    26     SET @i += 1 
    27 END 
    28 GO
    29 
    30 -- Create a unique non clustered index on the clustered table 
    31 CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID 
    32 ON Customers(CustomerName) 
    33 GO  
    34 
    35 -- Create a non-unique non clustered index on the clustered table 
    36 CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID 
    37 ON Customers(CustomerName) 
    38 GO
    复制代码

    在2个非聚集索引创建后,我们可以使用DMV sys.dm_db_index_physical_stats来查看索引的相关信息。

    复制代码
     1 -- Retrieve physical information about the unique non-clustered index 
     2 SELECT * FROM sys.dm_db_index_physical_stats 
     3 ( 
     4     DB_ID('ALLOCATIONDB'), 
     5     OBJECT_ID('Customers'), 
     6     2, 
     7     NULL, 
     8     'DETAILED'
     9 ) 
    10 GO
    11 
    12 -- Retrieve physical information about the non-unique non-clustered index 
    13 SELECT * FROM sys.dm_db_index_physical_stats 
    14 ( 
    15     DB_ID('ALLOCATIONDB'), 
    16     OBJECT_ID('Customers'), 
    17     3, 
    18     NULL, 
    19     'DETAILED'
    20 ) 
    21 GO
    复制代码

    我们可以看到,唯一非聚集索引的记录长度是107 bytes,非唯一非聚集索引的记录长度是111 bytes。因此这2个索引的内部存储格式肯定不同。我们从唯一非聚集索引开始分析。

    我们可以通过DBCC IND命令找出索引根页,聚集索引的INDEX ID为1,非聚集索引的INDEX ID从2开始,依次递增,这里应该是2和3。

    复制代码
    1 TRUNCATE TABLE dbo.sp_table_pages
    2 INSERT INTO dbo.sp_table_pages
    3 EXEC('DBCC IND(ALLOCATIONDB, Customers, -1)') 
    4 
    5 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC
    复制代码

    从这里我们可以看出,唯一非聚集索引的根页是20834,非唯一非聚集索引的根页是21890。

    我们看下唯一非聚集索引的根页内容:

    1 DBCC PAGE(ALLOCATIONDB, 1, 20834, 3)
    2 GO

    从图中我们可以看到,每条索引记录包含非聚集键(这里是唯一的)——即CustomerName列。

    我们换参数1再来看看根页信息:

    1 DBCC TRACEON(3604)
    2 DBCC PAGE(ALLOCATIONDB, 1, 20834, 1)
    3 GO

    这里的107 bytes包含下列信息:

    • 1 byte: 状态位
    • n bytes:非唯一聚集索引键——这里是CustomerName列,100 bytes
    • 4 bytes:页ID(PageID)
    • 2 bytes:文件ID(FileID)

    在唯一非聚集索引里,所有非叶子层的每条索引记录都包含这107 bytes信息。因此,你的非聚集索引键大小会影响到每个索引页可以存储多少条索引记录。这样的话,这个例子的CHAR(100),并不是一个很好的索引键。

    我们继续往下看,索引叶子层的存储情况:

    复制代码
    1 DBCC PAGE(ALLOCATIONDB, 1, 20834, 3)--根层
    2 GO
    3 
    4 DBCC PAGE(ALLOCATIONDB, 1, 20833, 3)--中间层
    5 GO
    6 
    7 DBCC PAGE(ALLOCATIONDB, 1, 21098, 3)--叶子层
    8 GO
    复制代码

    从图中我们可以看到,SQL Server在叶子层这里保存聚集键(即CustomerID列的值)。这个值是SQL Server用来指向聚集索引里对应记录的指针。手上有了这个值,SQL Server就可以在聚集索引找到对应记录——通过聚集索引查找(Clustered Index Seek)运算符。这和在堆表上定义的非聚集索引有重大区别。因为在堆表里,SQL Server使用叶子层的HEAP RID直接指向数据页里存储的对应记录。因此,SQL Server不用访问额外索引,就可以直接正确读取到数据页。

    这也意味着SQL Server在堆表上通过非聚集索引找记录,比在聚集表上通过非聚集索引找记录快很多,因为SQL Server不需要执行额外的聚集索引查找(Clustered Index Seek)运算符。因此在堆表上可以读取更少的页正确找到记录。当不要高估这个细节,想想在堆表通过使用非聚集索引,性能上可以有多少好处。事实上,SQL Server总是尽量把索引页放在缓存区管理器里,因此对于SQL Server来说,使用额外的聚集索引查找(Clustered Index Seek)从聚集索引里找回记录,成本更低。

    现在我们来分析下非唯一非聚集索引。先来看看根页:

    1 DBCC PAGE(ALLOCATIONDB, 1, 21890, 3)
    2 GO

    从上图可以看出,非唯一非聚集索引根页里,SQL Server这里保存里非聚集索引键和聚集索引键,这个和刚才的唯一聚集索引根页是不一样的。

    SQL Server这里需要使用唯一聚集键来使非唯一非聚集索引键唯一。这在非唯一非聚集索引的每一层都会保存,从索引根页到叶子层。这就是说你需要更多的存储空间来保存索引,因为SQL Server在每条索引记录里不仅保存唯一聚集键,也保存非唯一非聚集索引键。因此当你选择不好的聚集键(像 CHAR(100)等)时,情况会变得更糟。

    复制代码
    1 DBCC PAGE(ALLOCATIONDB, 1, 21890, 3)--根层
    2 GO
    3 
    4 DBCC PAGE(ALLOCATIONDB, 1, 21889, 3)--中间层
    5 GO
    6 
    7 DBCC PAGE(ALLOCATIONDB, 1, 22087, 3)--叶子层
    8 GO
    复制代码

    我们换参数1再来看看根页信息:

    1 DBCC TRACEON(3604)
    2 DBCC PAGE(ALLOCATIONDB, 1, 21890, 1)
    3 GO

    这111 bytes包括:

    • 1 byte:状态位
    • n bytes:非唯一非聚集索引键——这里是CustomerName列,100 bytes
    • n bytes:唯一聚集索引键——这里是CustomerID列,4 bytes
    • 4 bytes:页ID(PageID)
    • 2 bytes:文件ID(FileID)

    当你把这些字节长度汇总后,你就得到了刚才提到的111 bytes。因此在你创建非唯一非聚集索引时,就要考虑到这些额外存储,因为它会影响到你的非聚集索引的每一层。

    在这个系列的下篇文章里,我们最后来看下在非唯一聚集索引上,唯一和非唯一非聚集索引的区别,请继续关注!


    本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4565906.html,如需转载请自行联系原作者

    展开全文
  • 分析表的索引建立情况:DBCC showcontig('...1、没有聚集索引查询 2、有聚集索引查询 3、没有非聚集索引查询 4、有非聚集索引查询 5、查询表索引语句 EXEC sp_helpindex A 6、删除索引语句DROP inde...
  • 在上篇文章里,我讨论了唯一和非唯一聚集索引的区别。...当我们在表上定义PRIMARY KEY约束时,SQL Server会为我们创建唯一聚集索引;另外我们可以通过CREATE UNIQUE CLUSTERED INDEX语句在表上创建唯一...
  • 在上一篇文章里,我们讨论了堆表上唯一/非唯一非聚集...这篇文章里,我想谈下唯一和非唯一聚集索引之间的区别,这2类聚集索引对存储的影响。 看这个文章之前,希望你对聚集索引有个基本的认识,并且知道堆表和聚集表...
  • 创建聚集索引

    2014-11-14 06:11:00
    创建聚集索引 SQL Server 2014 其他版本 2(共 2)对本文的评价是有帮助-评价此主题 您可以使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 2014 中创建聚集...
  • sql 创建聚集索引和非聚集索引

    万次阅读 2017-09-19 13:31:24
    --创建聚集索引create CLUSTERED INDEX 索引名称 ON 表名(字段名) --创建聚集索引 create NONCLUSTERED INDEX 索引名称 ON 表名(字段名) --删除指定约束alter table 表名 drop constraint 主键约束名称 --...
  • 如果不存在该表的聚集索引,则可以指定唯一聚集索引。 有关详细信息,请参阅 Unique Constraints and Check Constraints。 独立于约束的索引 默认情况下,如果未指定聚集,将创建非聚集索引。 对于每个...
  • 当时我的回答是这样的:“主键就是加了唯一性约束的聚集索引。” “你确定你所说的是对的?” 面试官反问到。 “应该是对的。” 我不加思索地回答道。 “你回去后研究一下这个问题吧。”  难道我真的错了? 第一...
  • 聚集索引、非聚集索引、非聚集唯一索引  我们都知道建立适当的索引能够提高查询速度,优化查询。先说明一下,无论是聚集索引还是非聚集索引都是B树...这时主键将会被作为一个唯一的非聚集索引(唯一索引)被创建。...
  • 五、聚集索引和非聚集索引 一、索引概述 索引:是一个单独的、物理的数据库结构,它是某个表中一列或若干类的集合相应的指向表中物理标识这些值的数据页的逻辑指针清单,索引是依赖于表建立的,它提供了数据库中...
  • 但是,由于每个表只允许使用一个聚集索引,因此按照这个简单的逻辑来确定要创建哪种类型的索引并不总能成功。对于该问题有一个简单的物理原因。对于聚集索引 B 树结构的上部(非叶层),如果像对它们的非聚集索引...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 25,306
精华内容 10,122
关键字:

创建唯一聚集索引的语句是