I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL Server to do so, instead of doing all in the Index scan?
For an example:
The index already contains all the fields used by the WHERE. I expanded the Index Scan operation, and it execute part of the where with some parameters involved (totally expected). I then expanded and inspected the Filter operation, and what it does is to apply some other filtering on a couple datetime fields already evaluated by the Index Scan, plus some other conditions on some other SP parameters.
What's going on here? I obviously don't care about my specific query, that's not the point, I'm trying to understand what conditions can cause this to happen.