title: SQL Server not using index for top + order by
body: <p>Query:</p>
  <pre><code>SELECT TOP 10 
      [Trade_US_PC].[ID] 
  FROM 
      [Trade_US_PC] 
  INNER JOIN 
      [Item] ON ([Trade_US_PC].[ItemID] = [Item].[ID]) 
  WHERE 
      ([Trade_US_PC].[ItemTraitsID] = 14) 
  ORDER BY 
      [Item].Name_EN 
  </code></pre>
  <ul>
  <li><code>Trade_US_PC.ID</code> and <code>Item.ID</code> are primary keys</li>
  <li><code>[Trade_US_PC].[ItemID]</code> is a foreign key on <code>Item.ID</code> and is indexed <code>CREATE INDEX IX_Trade_US_PC_ItemID ON Trade_US_PC(ItemID);</code></li>
  <li><code>[Trade_US_PC].[ItemTraitsID]</code> is a Nullable column foreign key on another table, and is Spatial Indexed <code>CREATE NONCLUSTERED INDEX FTIX_Trade_US_PC_ItemTraitID ON Trade_US_PC(ItemTraitsID) WHERE ItemTraitsID IS NOT NULL;</code></li>
  <li><code>Item.Name_EN</code> is indexed <code>CREATE INDEX IX_Item_Name_EN ON Item(Name_EN)</code></li>
  </ul>
  <p>Problem is that the query is not using the <code>Name_EN</code> index when performing <code>Top 10</code> and <code>order by</code>.</p>
  <p><a href="https://i.stack.imgur.com/6Y3k8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6Y3k8.png" alt="enter image description here" /></a></p>
  <p>What can I do to get rid of the costly Top N Sort?</p>