The uploaded plan performs a separate seek into the target table for each value specified in the IN list, then performs a distinct sort to honour the duplicate-eliminating semantics of IN (side note: value 88 is listed twice in the sample).
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 ID >= 1 AND ID <= 108:
This results from the application of "implied predicate" logic. SQL Server can see that the smallest value in the list is 1 and the largest value is 108. It creates the implied predicate ID >= 1 AND ID <= 108 from this observation.
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.
Update:
The execution plan operates as follows:
Execution starts with the sort, which asks for a row from the nested loops join
The join asks for a row from the Constant Scan (value returned = 1)
The join passes control to the Seek, providing the correlation value (1)
The seek finds the first row matching ID = 1
The seek applies the residual predicate ID >= 1 AND ID
The row passes back through the join to the sort
The sort asks for another row from the join
The next row matching ID = 1 is returned from the seek
When no more rows come from the seek...
The join asks for a row from the Constant Scan (value returned = 2)
The join passes control to the Seek, providing the correlation value (2)
The seek finds the first row matching ID = 2
The seek applies the residual predicate ID >= 1 AND ID
...
This process continues until the sort has all rows from its subtree
The distinct sort is performed and then starts returning rows to the client
I hope that clarifies things.
