Rob Farley digs into what happens with a seek operation:
Let’s go back to our original query, looking for address types 2, 4, and 5, (which returns 2 rows) and think about what’s going on inside the seek.
I’m going to assume the Query Engine has already done the work to figure out that the Index Seek is the right operation, and that it has the page number of the index root handy.
At this point, it loads that page into memory, if it’s not already there. That’s the first read that gets counted in the execution of the seek. Then it locates the page number for the row it’s looking for, and reads that page in. That’s the second read.
But we often gloss over that ‘locates the page number’ bit.
The upshot is rather interesting: in certain edge cases, an uglier query can be better than an easier-to-understand query. If you do this, however, you definitely want to document it; otherwise, you’ll leave the next maintainer (which could be you!) confused.