How can one turn an index scan into a seek?

avatar image By MaricopaJoe 1 asked Jan 16 at 06:11 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 16 at 06:38 PM

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)?

avatar image Hugo Kornelis Jan 17 at 09:25 AM

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.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x676
x489
x31

asked: Jan 16 at 06:11 PM

Seen: 117 times

Last Updated: Jan 17 at 09:25 AM