title: Why is this an Index Scan and not a Index Seek?
body: <p>Here's the query:</p>
  
  <pre><code>SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
  FROM        dbo.Locations a
  INNER JOIN  dbo.LocationCache b ON a.LocationId = b.LocationId
  WHERE       a.CountryId = 2
  AND         a.Type = 7
  </code></pre>
  
  <p><strong>Location  Indexes:</strong></p>
  
  <p>PK_Locations: </p>
  
  <blockquote>
    <p>LocationId</p>
  </blockquote>
  
  <p>IX_Locations_CountryId_Type: </p>
  
  <blockquote>
    <p>CountryId, Type</p>
  </blockquote>
  
  <p><strong>LocationCache  Indexes:</strong></p>
  
  <p>PK_LocationCache: </p>
  
  <blockquote>
    <p>LocationId</p>
  </blockquote>
  
  <p>IX_LocationCache_LocationId_SearchQuery_SearchRank: </p>
  
  <blockquote>
    <p>LocationId, SearchQuery, SearchRank</p>
  </blockquote>
  
  <p><strong>Execution Plan:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/rnbKw.png" alt="enter image description here"></p>
  
  <p>So it's doing a <strong>Index Seek</strong> on Locations, using the covering index, cool.</p>
  
  <p>But why it is doing a <strong>Index Scan</strong> on the LocationCache covering index?</p>
  
  <p>That covering index has LocationId, SearchQuery, SearchRank in the index (not as "Included columns").</p>
  
  <p>Hover on the index scan:</p>
  
  <p><img src="https://i.stack.imgur.com/JXaNp.png" alt="enter image description here"></p>
  
  <p>This query needs to go in an indexed view served by a SQL Server FTS catalogue, consumed by an autocomplete plugin, so it needs to be 100% optimized.</p>
  
  <p>At the moment that above query is taking 3 seconds. It should be &lt; 0.</p>
  
  <p>Any ideas?</p>