title: query optimizer operator choice - nested loops vs hash match (or merge)
body: <p>One of my stored procedures was taking too long execute. Taking a look at query execution plan I was able to locate the operation taking too long. It was a nested loop physical operator that had outer table (65991 rows) and inner table (19223 rows). On the nested loop it showed estimated rows = 1,268,544,993 (multiplying 65991 by 19223) as below:</p>
  
  <p><img src="https://i.stack.imgur.com/MfaML.png" alt="enter image description here"></p>
  
  <p>I read a few articles on physical operators used for joins and got a bit confused whether nested loop or hash match would have been better for this case. From what i could gather:</p>
  
  <p>Hash Match - is used by optimizer when no useful indexes are available, one table is substantially smaller than the other, tables are not sorted on the join columns. Also hash match might indicate more efficient join method (nested loops or merge join) could be used.</p>
  
  <p><strong>Question:</strong> Would hash match be better than nested loops in this scenario?</p>
  
  <p>Thanks </p>