SQL Server bringing back inconsistent results

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).

This view is incredibly large and unwieldy, and for now, I won't post it because I thin开发者_JAVA百科k 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.

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:

SELECT Count(*) FROM
(
SELECT * FROM vw_Data1
UNION
SELECT * FROM vw_Data2
)

then I should get back the same number as if I just did

SELECT Count(*) FROM vw_Data1

as long as vw_Data1 and vw_Data2 return identical rows (which is what I want to check).

However, what I am finding is if I run the UNION query above several times, I get DIFFERENT RESULTS EACH TIME.

So, just to be clear, if I run:

SELECT Count(*) FROM
(
SELECT * FROM vw_Data1
UNION
SELECT * FROM vw_Data2
)

more than once, then I get different results each time.

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?

There is one non-deterministic function used, and that is as part of the following (horrible) join:

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 

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!