CODEWITHSUNDEEP
sqlt-sql
dasisderblyme
dasisderblyme

Reputation: 1194

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’

Upvotes: 1

Views: 38

Answers (2)

Ali Eshghi
Ali Eshghi

Reputation: 1243

when you utilize it as function like you first select , you can't, but for 2nd select if you used clustered index then you can(this is why I asking the type of index you used...)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270633

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.

Upvotes: 2

Related Questions