SQL Server - Predicate and Seek Predicate on the same column

Question

My table has an non-clustered index: IX_columnA_columnB

CREATE INDEX IX_columnA_columnB ON table (columnA, columnB)
    INCLUDE (xxx)

My query has filter on both columnA and columnB On execution plan, the optimizer use seek predicate on both them, but I see columnB appears again in predicate I don't know this case. Anybody can help? Thanks so much

Answer 1

Your query predicate is something like

recommend Between 179764 and 26506209 and winlostdate >= '2017-07-12'

The index is logically ordered as follows.

+-----------+-------------+------+----------+
| recommend | winlostdate | Seek | Residual |
+-----------+-------------+------+----------+
|    179764 | 2000-01-01  |      |          |
|    179764 | 2017-07-12  | Y    | Y        |
|    179765 | 2000-01-01  | Y    | N        |
|    179765 | 2017-07-12  | Y    | Y        |
|  26506209 | 2000-01-01  | Y    | N        |
|  26506209 | 2017-07-12  | Y    | Y        |
|  26506210 | 2000-01-01  |      |          |
+-----------+-------------+------+----------+

The seek predicate has a start predicate of (recommend, winlostdate) >= (179764, '2017-07-12') and reads the index in order until the end condition is reached and recommend <= 26506209 is not true.

This is the contiguous range where Seek=Y above.

However that range includes two rows that don't match the predicate over all. These are discarded by the Residual predicate on winlostdate >= '2017-07-12'