title: why is sql server doing nested loops
body: <p>I have 2 tables facts and customer, with 422,000 and 350,000 rows.</p>
  
  <p>When running this query, the joining used is Hash Match:</p>
  
  <pre><code>SELECT SUM(sales), [state], County
  FROM pp.Facts f
  INNER JOIN pp.Customer c ON (c.customerKey = f.customerKey)
  WHERE c.County IN (N'Nassau', N'Westchester', N'Erie', N'Orange', N'Union', 
                     N'Santa Clara', N'San Diego', N'Essex', N'Morris', 
                     N'Dallas', N'Allegheny', N'Bucks')
  GROUP BY [state], County
  </code></pre>
  
  <p>And it works great.</p>
  
  <p>When running is query (same, but with more items in the filter), the joining changes to Nested Loops and never comes back (obviously)</p>
  
  <pre><code>SELECT SUM(sales), [state], County
  FROM pp.Facts f
  INNER JOIN pp.Customer c ON (c.customerKey = f.customerKey)
  WHERE c.County IN (N'Nassau', N'Westchester', N'Erie', N'Orange', N'Union', 
                     N'Santa Clara', N'San Diego', N'Essex', N'Morris', 
                     N'Dallas', N'Allegheny', N'Bucks', N'New York', 
                     N'Bergen', N'Montgomery', N'Harris', N'Delaware', N'San 
                     Francisco', N'Suffolk', N'Travis', N'Middlesex', 
                     N'Bexar', N'Tarrant', N'Los Angeles', N'Philadelphia')
  GROUP BY [state], County
  </code></pre>
  
  <p>Why is the joining method changed?</p>
  
  <p>There is a FK defined on the customerKey columns and a PK on customer.customerKey (no additional indexes)</p>