title: When will SQL Server Choose a Hash Join over a Merge Join?
body: <p>I have a large query with many joins that I am trying to tune, and one warning sign is that there are many, many hash joins being used throughout. I went down to the base of the query tree and went to the first join there, which is an inner join.</p>
  <p>Table A is using a clustered index scan to retrieve its data, which is sorted on the join column.
  Table B is using a nonclustered index scan, which is also sorted on the join column.</p>
  <p>When I join just these two tables in isolation, and select the same set of columns, the optimizer uses a merge join. The sets being joined are approximately the same size, and not very large (&lt;5,000 rows).</p>
  <p>What could explain the optimizer choosing the hash join over the merge join in this case?</p>
  <p>EDIT</p>
  <p>As requested, I have added a few more details. The index definitions are:</p>
  <pre><code>CREATE NONCLUSTERED INDEX NCL_Asset_Issuer_MergeInduce ON Asset.IssuerCompanyId (CompanyId)INCLUDE (IsPrivate,HasPublicEquity,Ticker,FinancialTemplateID,BondTicker,SICOther1ID,SICOther4ID,SICSandPID,SICOther3ID,SICMoodyID,CurrencyTypeID,SecondaryAnalystID,AnalystID,SICOshaID,SecondaryBondTicker,FiscalYearEnd,EquityExchangeID);
  
  CREATE NONCLUSTERED INDEX NCL_Asset_IssuerCustom_IssuerId ON Asset.IssuerCustom (IssuerID) INCLUDE (Text3,ListItem1ID,ListItem5ID,ListItem3ID,ListItem2ID,Version,text4,TextLong15,ListItem6ID)
  </code></pre>
  <p>The following query will return a merge join, as I mentioned earlier:</p>
  <pre><code>SELECT IsPrivate,HasPublicEquity,Ticker,FinancialTemplateID,BondTicker,SICOther1ID,SICOther4ID,SICSandPID,SICOther3ID,SICMoodyID,CurrencyTypeID,SecondaryAnalystID,AnalystID,SICOshaID,SecondaryBondTicker,FiscalYearEnd,EquityExchangeID,ic.ListItem2Id,ic.ListItem3ID,ic.IssuerId
  FROM Asset.Issuer i
  INNER JOIN Asset.IssuerCustom ic ON i.CompanyId = ic.IssuerId;
  </code></pre>
  <p><img src="https://i.stack.imgur.com/GP0uy.png" alt="enter image description here" /></p>
  <p>As you can see, the query is using both the indices above. On the other hand, this same join occurs in a much larger query, and the below image shows the corner of the plan, where this join is occurring as a hash join:</p>
  <p><img src="https://i.stack.imgur.com/hZhlv.png" alt="enter image description here" /></p>
  <p>The one difference that I can see is that there is a reversal in terms of which table is the &quot;inner&quot; table vs which is the &quot;outer&quot; table. Still, why would this impact the execution plan if both queries are inner joins on the same column?</p>