I am using Adventureworks database as a testing
I created a filtered index
CREATE NONCLUSTERED INDEX NCI_Department
WHERE Title= 'Marketing Manager'
FROM HumanResources.Employee he
WHERE he.Title = 'Marketing Manager'
When i checked the execution plan, it is going for key lookup .Clustered.
WHy should it go for key lookup?
Select is using employeeID,LoginID and Title.
My index has employeeID, loginID and title in where clause.
Also, employeeID should not be required as employeeID is already clustered index key and it will be in NCI as well.
The reason behind raising this bug is why it is going for key lookup. Isn't it getting all the data required for fetching the select.