How can one turn an index scan into a seek?

MaricopaJoe 2018-01-16 18:11:33

Aaron Bertrand 2018-01-16 18:38:53
Any chance you can send a non-anonymized plan, and table structure? Are both distincts necessary (e.g. there will really be duplicates of those narrow column sets)?
Hugo Kornelis 2018-01-17 09:25:50
The real question, IMO, is not HOW to turn an index scan into an index seek. The real question is whether you want to.
And at this point I am pretty sure the answer is no.

It's hrd to draw many conclusion from what you posted because (a) it is anonymized which hides a lot of helpful information; and (b) it is an estimated plan only so we cannot see if there are any critical mis-estimates. But I do see that the estimated number of rows is equal to the table cardinality, which matches the absence of a WHERE clause anywhere in the query,

In other words: your query directs SQL Server to read all rows in the entire table. An index scan is far more efficient for that than an index seek.