CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL ; GO
筛选索引 FIBillOfMaterialsWithEndDate
对下面的查询有效。 您可以显示查询执行计划,以确定查询优化器是否使用了该筛选索引。
SQL复制
SELECT ProductAssemblyID, ComponentID, StartDate FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL AND ComponentID = 5 AND StartDate > '20080101' ;
ProductAssemblyID字段没有包含在筛选索引里面,应该回表查询才对,但是执行计划并没有回表,非常迷惑.
ProductAssemblyID也并非主键
改为SELECT *...
SELECT * FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL AND ComponentID = 1 AND StartDate > '20080101' ;