时间戳

首页 > 解决方案 > Does the server execution plan use the index

sql - Does the server execution plan use the index

问题描述

I have added an index on the column “LastChangeDate” in table dbo.Employee. Will the SQL Server execution plan use this index in both the cases below? Can somebody explain why or why not. 

SELECT EmployeeName  
FROM dbo.Employee  
WHERE DATEADD(MONTH, -2, LastChangeDate) >= ‘2016-01-01’ 

SELECT EmployeeName  
FROM dbo.Employee  
WHERE LastChangeDate >= ‘2016-01-01’

标签： sqltsql

解决方案

The first cannot use an index, because LastChangeDate is the argument to a function. The only function (as far as I know) in SQL Server that does not disallow an index is conversion to a date.

The second might or might not use an index. If you have a clustered index on LastChangeDate, then it should. However, the query is presumably not very selective -- meaning that it selects a significant portion of all rows. In such circumstances, the database optimizer may decide to forego the index and do a full table scan.

推荐阅读