Here in the query optimizer know what exact range goona come but insted of chosing seek predicate it is choosing predicate here,
PS: just testing , i know chang the syntax will create a better and optimal plan
By NEERAJ PRASAD SHARMA 50 asked Sep 16, 2014 at 11:39 AM
The uploaded plan performs a separate seek into the target table for each value specified in the
If I understand correctly, your question is why, when SQL Server performs a seek on each specific value, it also applies a residual predicate testing
This results from the application of "implied predicate" logic. SQL Server can see that the smallest value in the list is
The "implied predicate" mechanism is quite general. It can enable significant optimizations in other scenarios. Here, the residual predicate adds no value at all. It's presence in the final plan is a small redundancy that will have little effect on observed performance.
Undocumented trace flag 2324 can be used to disable implied predicates, in case you are interested.
The execution plan operates as follows:
I hope that clarifies things.
By SQLkiwi ♦ 6.5k answered Sep 16, 2014 at 02:01 PM