sql-server sql-server-2019
Forrest
Consider the following 

<https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ed52053f320f9d35f575efe6c14a0e59>

```
CREATE TABLE #A (ID int primary key)
CREATE TABLE #B (ID int primary key)

INSERT #A SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0) FROM sys.columns
INSERT #B SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0) FROM sys.columns

SELECT *
FROM #A a
JOIN #B b
ON a.ID = b.ID
WHERE a.ID IN (1,10)
```

There are two seeks into #A to find the IDs, each followed by a seek in #B to find the matching ID. However, there's an extra predicate in the seek on #B.

![Capture.PNG](/image?hash=3a07dc86dd8c947d2d627398c32ffe909891d6dfc19b94c74d75caf77962acab)

![surprise predicate.png](/image?hash=37aaad1a7d2f2b689e903857602b8dbf1f9f45b99e3bb7e3edbbc5ccaf432173)

Shouldn't the Seek Predicate be enough to guarantee correctness? What's the purpose of this extra predicate?

Top Answer
Martin Smith
The extra predicate on `#B` is added during query simplification by the "implied predicate" mechanism.

It sees that `a.ID IN (1,10)` and `a.ID = b.ID` so adds an implied predicate that `b.ID BETWEEN 1 AND 10`. A similar case is [discussed here](https://answers.sqlperformance.com/questions/2299/why-not-seek-predicate.html).

It could of course be more specific and just add an implied predicate of `b.ID IN (1,10)` but I guess the range is simple to implement irrespective of number of values in the `IN` clause and is good enough for restricting the rows read from `B` if doing a join type other than nested loops (i.e. `MERGE` join or `HASH` join).

For the nested loops plan in this example it is not useful. An example where this type of implied predicate *is* useful is [in the article here](https://www.brentozar.com/archive/2017/11/implied-predicate-partition-elimination/)


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.