这篇文件主要介绍使用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