title: Does UNION or UNION all build one massive query that locks all tables selected?
body: <p>I'm being told by my lead DBA that I wrote poorly formed code because I used a UNION ALL to accumulate results of successive queries on different tables.  I thought when a query with multiple select statements that had results UNIONed executed separately so when each select statement executes it places a shared lock on the table that is released when finished and the next select starts.
  I thought the results were accumulated in some buffer or tmp table.</p>
  
  <p>Would someone kindly tell me what goes on behind the scenes and what resources consumed when a results of a hundred select statements are UNIONed.  Each select operates on one table and collects schema, Table, and Column names.</p>
  
  <p>Sorry, I don't have query plan.  The DBA complained the query was too big to show much of the plan.  His comments are below the query.</p>
  
  <pre><code>SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'PersonModTextStaffSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [PersonModTextStaffSID] = -1  
  UNION ALL 
  SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'LabDataLabSubjectSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [LabDataLabSubjectSID] = -1  
  UNION ALL 
  SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'LabDataPatientSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [LabDataPatientSID] = -1  
  UNION ALL 
  SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'PersonModTextStaffSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [PersonModTextStaffSID] = -1  
  UNION 
  ALL SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'LabDataLabSubjectSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [LabDataLabSubjectSID] = -1  
  UNION ALL 
  SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'LabDataPatientSID' as TheColumn, COUNT(*) as NullCount 
  FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [LabDataPatientSID] = -1  
  UNION ALL 
  </code></pre>
  
  <p>In any case the query above could have certainly been written in a much more efficient way. As written for every table in the query it will scan the entire table for every UNION which is 791 times. Just looking at the first few lines of the query we can see these are just count’s from the same table which could have been done with a single scan of this table using a CASE expression for the count and you would have gotten all the counts in one pass per table. 
  The bottom line is that right now we only have a few users on the FRE and processes like this are already affecting many  users / jobs. Imagine when we have hundreds to thousands of users. We simply can’t afford to run processes that are not vetted or properly tested like these two examples. This is nothing personal and should not be taken as such, it is all about the overall well being of the server and all the users. It is part of my job to point out such issues so they can be addressed when I see them and this is unquestionably one of those times. These can’t be run again until they are rewritten to ensure they do what they are intended to do and that they are efficient enough to not cause issues with other processes.</p>