在SQL Server中创建条件索引

这篇文件主要介绍使用T-SQL在SQL Server中创建条件索引。条件索引是一个优化了非聚集索引，特别适合特定数据子集的数据查询。条件索引就是使用过滤条件来索引部分表格数据。一个设计良好的条件索引可以查询的性能，并减少索引存储的大小。

一、条件索引有如下的好处：

• 提高查询性能和计划质量
• 减少索引维护成本
• 减少索引存储成本

二、设计需要考虑的事情

1、当一列只查询少数数据值的时候，你可以创建这些数据值的子集。

2、当表有异构数据库行的时候，可以为多个数据种类创建条件查询。

三、限制

1、不能在视图上创建条件索引

2、条件索引有如下好处：

• 减少索引维护成本：查询处理器使用更少的CPU资源来更新条件索引
• 提升计划质量：查询优化器优先考虑条件索引
• 在线索引重建：条件索引可以在线重建，而索引的视图不能在线重建
• 非唯一索引：条件索引可以不是唯一索引

3、只支持简单比较操作

4、如果过滤索引表达式同查询谓词，且不返回条件索引中的列，则条件索引表达式中的列不必是主键，也不必是包含列

5、如果查询谓词使用的比较列不同于条件索引表达式，则条件索引表达式中的列必须是主键或者包含列

6、如果列在查询结果里面，则条件索引表达式中的列必须是主键或者包含列

7、表格的聚集索引不必是主键或包含列，聚集索引自动包含在非聚集索引中，包括条件索引

四、创建条件查询

use AdventureWorks2012

go

create nonclustered index FilterBillOfEndDateIndex

on ProductionBillOfMaterials(ComponentID, StartDate)

where EndDate is not null;

GO

五、使用条件查询

use AdventureWorks2012

go

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '01/01/2008' ;  
GO

6、在查询中指定条件索引

use AdventureWorks2012

go

select ComponentID, StartDate from Production.BillOfMaterials

with(index(FilterBillOfEndDateIndex))

where EndDate in ('20000085', '2000000', '20000918');

GO