title: SQL Server bringing back inconsistent results
body: <p>I am working on some reports that were created before I started in my current job. One of these reports is based on a view (SQL Server 2005).</p>
  
  <p>This view is incredibly large and unwieldy, and for now, I won't post it because I think it's just too big. I'm not sure how it was produced - I'm guessing that it was produced in the designer because I can't see someone actually writing stuff like this. It's several pages long, and references 5 other views. Bottom line - it's complicated, and needs to be refactored/redesigned, but until we get time for that we're stuck with it.</p>
  
  <p>Anyway, I have to make some minor non-functional changes to it in order to move it to a different database and schema. In order to make sure I'm not changing what it actually returns, I'm amending a second version of the view. Let's call the first view vw_Data1 and my new view vw_Data2. Now, if I write:</p>
  
  <pre><code>SELECT Count(*) FROM
  (
  SELECT * FROM vw_Data1
  UNION
  SELECT * FROM vw_Data2
  )
  </code></pre>
  
  <p>then I should get back the same number as if I just did</p>
  
  <pre><code>SELECT Count(*) FROM vw_Data1
  </code></pre>
  
  <p>as long as vw_Data1 and vw_Data2 return identical rows (which is what I want to check).</p>
  
  <p>However, what I am finding is if I run the UNION query above several times, I get DIFFERENT RESULTS EACH TIME.</p>
  
  <p>So, just to be clear, if I run:</p>
  
  <pre><code>SELECT Count(*) FROM
  (
  SELECT * FROM vw_Data1
  UNION
  SELECT * FROM vw_Data2
  )
  </code></pre>
  
  <p>more than once, then I get different results each time.</p>
  
  <p>As I say, I'm not posting the actual code yet, because the first thing I want to ask is simply this - how on earth can a query return different results?</p>
  
  <p>There is one non-deterministic function used, and that is as part of the following (horrible) join:</p>
  
  <pre><code>LEFT OUTER JOIN dbo.vwuniversalreportingdata_budget 
       ON 
  
  
  
  CASE
      WHEN dbo.f_tasks.ta_category = 'Reactive' THEN
          CAST(dbo.f_tasks.ta_fkey_fc_seq AS VARCHAR(10)) 
              + ' | ' 
                  + CAST(dbo.f_tasks.ta_fkey_fcc_seq AS VARCHAR(10)) 
                      + ' | ' 
                          + CAST(YEAR(DATEADD(MONTH, -3, dbo.f_tasks.ta_sched_date)) AS VARCHAR(10))
      WHEN dbo.f_tasks.ta_category = 'Planned' THEN
          CAST(dbo.f_tasks.ta_fkey_fc_seq AS VARCHAR(10)) 
              + ' | ' 
                  + CAST(dbo.f_tasks.ta_fkey_fcc_seq AS VARCHAR(10)) 
                      + ' | ' 
                          + CAST(YEAR(DATEADD(MONTH, -3, dbo.f_tasks.ta_est_date)) AS VARCHAR(10))
      WHEN dbo.f_tasks.ta_category = 'Periodic' THEN
          CAST(dbo.f_tasks.ta_fkey_fc_seq AS VARCHAR(10)) 
              + ' | ' 
                  + CAST(dbo.f_tasks.ta_fkey_fcc_seq AS VARCHAR(10)) 
                      + ' | ' 
                          + CAST(YEAR(DATEADD(MONTH, -3, dbo.f_tasks.ta_est_date)) AS VARCHAR(10))
  END
  = dbo.vwuniversalreportingdata_budget.id 
  </code></pre>
  
  <p>The whole query is pretty disgusting like this. Anyway, any thoughts on how this could happen would be gratefully received. Is it something to do with the union, perhaps? I don't know. Help!</p>