Sql Server 索引之唯一索引和筛选索引

唯一索引（UNIQUE  INDEX） 

当主键创建时如果不设置为聚集索引，那么就一定是唯一的非聚集索引。实际上，唯一索引，故名思议就是它要求该列上的值是唯一的。唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。 创建 UNIQUE 约束和创建与约束无关的唯一索引并没有明显的区别。进行数据验证的方式相同，而且对于唯一索引是由约束创建的还是手动创建的，查询优化器并不加以区分。 但是，创建列的 UNIQUE 约束会使索引目标更清晰。 Unique Constraints and Check Constraints." xml:space="preserve" id="mt6">在创建唯一索引时，可以设置一个忽略重复键的选项。 Yes and you attempt to create duplicate keys by adding data that affects multiple rows (with the INSERT statement), the row containing a duplicate is not added." xml:space="preserve" id="mt8">如果此选项已设置为“是”，当您试图通过添加影响多行的数据来创建重复键（使用 INSERT 语句）时，则不会添加包含重复项的行； No, the entire insert operation fails and all the data is rolled back." xml:space="preserve" id="mt9">如果此选项设置为“否”，则整个插入操作将失败，并且将回滚所有数据。

 

唯一索引的优点

• 多列唯一索引能够保证索引键中值的每个组合都是唯一的。 例如，如果为 LastName、FirstName 和 MiddleName 列的组合创建了唯一索引，则表中的任意两行都不会有这些列值的相同组合。
• 只要每个列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。
• 唯一索引能够确保定义的列的数据完整性。
• 唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。

典型实现

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

• PRIMARY KEY 或 UNIQUE 约束

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

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

• Unique Constraints and Check Constraints and Primary and Foreign Key Constraints. 

独立于约束的索引可以为一个表定义多个唯一非聚集索引。

• 索引视图

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

限制和局限

• 如果数据中存在重复的键值，则不能创建唯一索引、UNIQUE 约束或 PRIMARY KEY 约束。
• 唯一非聚集索引可以包括包含性非键列。

唯一索引依赖于唯一约束，删除唯一索引必须删除唯一约束。另外SQL Server又在建立唯一约束时又默认建立唯一索引。

过滤唯一索引，当我们需要既允许多个NULL值，又不允许重复的时候，可以使用这个：

　　CREATE UNIQUE NONCLUSTERED INDEX xx on

　　ProductDemo(<索引列>)　　--指定索引列

　　where <索引列>!=null)　　--过滤条件

　　对于用以上语法创建的唯一索引，插入时，只有当唯一索引列不为NULL的时候才检测重复。换句话说，以上表的索引列允许多个NULL值。

 

筛选索引（Filtered Index）

 筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。 筛选索引使用筛选谓词对表中的部分行进行索引。 与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

筛选索引与全表索引相比具有以下优点：

• 提高了查询性能和计划质量

设计良好的筛选索引可以提高查询性能和执行计划质量，因为它比全表非聚集索引小并且具有经过筛选的统计信息。 与全表统计信息相比，经过筛选的统计信息更加准确，因为它们只涵盖筛选索引中的行。

• 减少了索引维护开销

仅在数据操作语言 (DML) 语句对索引中的数据产生影响时，才对索引进行维护。 与全表非聚集索引相比，筛选索引减少了索引维护开销，因为它更小并且仅在索引中的数据更改时才进行维护。 筛选索引的数量可以非常多，特别是在其中包含很少更改的数据时。 同样，如果筛选索引只包含频繁修改的数据，则索引大小较小时可以减少更新统计信息的开销。

• 减少了索引存储开销

在没必要创建全表索引时，创建筛选索引可以减少非聚集索引的磁盘存储开销。 可以使用多个筛选索引替换一个全表非聚集索引而不会明显增加存储需求。

设计注意事项

• 在列中只有少量相关值需要查询时，可以针对值的子集创建筛选索引。 例如，当列中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时，可以为非 NULL 数据行创建筛选索引。 由此得到的索引与对相同键列定义的全表非聚集索引相比，前者更小且维护开销更低。
• 表中含有异类数据行时，可以为一种或多种类别的数据创建筛选索引。 通过将查询范围缩小为表的特定区域，这可以提高针对这些数据行的查询性能。 此外，由此得到的索引与全表非聚集索引相比，前者更小且维护开销更低。

限制和局限

• 不能对视图创建筛选索引。 但是，查询优化器可以从对视图中引用的表定义的筛选索引中获益。 对于从视图中选择数据的查询，如果查询结果正确，查询优化器会考虑对此查询使用筛选索引。

筛选索引与索引视图相比，具有以下优点：

• 减少了索引维护开销。 例如，相对于索引视图而言，查询处理器使用更少的 CPU 资源便可更新筛选的索引。
• 改善了计划质量。 例如，在查询编译期间，查询优化器考虑使用筛选的索引的情况要比考虑使用等效的索引视图的情况多。
• 联机索引重新生成。 您可以在筛选的索引可用于查询时重新生成它们。 索引视图不支持联机索引重新生成
• 非唯一索引。 筛选索引可以是非唯一的，而索引视图必须是唯一的。

• 筛选索引是针对一个表定义的，仅支持简单比较运算符。 如果需要引用多个表或具有复杂逻辑的筛选表达式，则应创建视图。

• 如果筛选索引表达式等效于查询谓词并且查询并未在查询结果中返回筛选索引表达式中的列，则筛选索引表达式中的列不需要作为筛选索引定义中的键或包含列。
• 如果查询谓词在不与筛选索引表达式等效的比较中使用了筛选索引表达式中的某列，则该列应为筛选索引定义中的键或包含列。
• 如果筛选索引表达式中的某列在查询结果集中，则该列应为筛选索引定义中的键或包含列。
• 表的聚集索引键不需要是筛选索引定义中的键或包含列。 聚集索引键自动包含在所有非聚集索引（包括筛选索引）中。
• 如果筛选索引结果的筛选索引表达式中指定的比较运算符会导致隐式或显式数据转换，则转换发生在比较运算符的左边时，会出现错误。 解决方法是在比较运算符的右边编写包含数据转换运算符（CAST 或 CONVERT）的筛选索引表达式。

转载于:https://www.cnblogs.com/terrence/p/4607374.html