title: SQL Server query: Union vs Distinct union all performance
body: <p>Does SQL have a difference in performance between these two statements? </p>
  
  <pre><code>SELECT distinct 'A' as TableName, Col1, Col2, Col3 FROM A 
  UNION ALL 
  SELECT distinct 'B' as TableName, Col1, Col2, Col3 from B
  </code></pre>
  
  <p>versus</p>
  
  <pre><code>SELECT 'A' as TableName, Col1, Col2, Col3 FROM A 
  UNION 
  SELECT 'B' as TableName, Col1, Col2, Col3 from B
  </code></pre>
  
  <p>The difference between this and similar questions such as <a href="https://stackoverflow.com/questions/3853602/union-vs-distinct-in-performance">UNION vs DISTINCT in performance</a> is that I can confirm ahead of time that the individual tables I'm using won't have any duplicate records <em>between</em> them, only within the individual tables.</p>
  
  <p>The execution plans look the same to me, in that it sorts the individual tables before concatenating. However, if I remove the scalar from them both, the plan of the UNION ALL stays basically the same but the UNION changes to concatenating before the distinct. I'll be concatenating about 20 tables together, and it's not clear whether doing 20 individual DISTINCTs is faster than doing one large DISTINCT at the end, since I can still confirm that the tables would not share any duplicates between them (only within the same table).</p>