SQL Server except and ANSI NULLS

Question

I have refactored some views through user defined functions and I want to ensure they are the same.

I have written a small script to test this is the case:

SET ANSI_NULLS OFF
GO

SELECT TOP 1000 [DealNumber]
      ,...others
FROM OLD_VIEW
EXCEPT
SELECT TOP 1000 [DealNumber]
            ,...others
  FROM NEW_VIEW

When I apply this, I obtain a number of rows as the result. However, if I take a DealNumber from any of the rows and I run the following:

SET ANSI_NULLS OFF
GO

SELECT [DealNumber]
      ,...others
FROM OLD_VIEW
WHERE DealNumber = 'MyDealNumber'
EXCEPT
SELECT [DealNumber]
            ,...others
FROM NEW_VIEW
WHERE DealNumber = 'MyDealNumber'

this returns, as expected, an empty record set.

As an alternative approach, I have written a function Test_View_Correctness which does the former test. However if I run it like this:

SELECT TOP 1000 [DealNumber] from OLD_VIEW
where DealNumber IN (SELECT a.DealNumber 
                     FROM Test_View_Correctness(DealNumber) as a)

How can I ensure the except approach works?

Answer 1

You need to order by a unique set of columns—without that SQL never guarantees row order and the two parts of the query will potentially return completely different subsets of rows:

SELECT ... FROM ( SELECT TOP 1000 [DealNumber],...others 
                  FROM OLD_VIEW 
                  ORDER BY x,y,z ) a
EXCEPT
SELECT ... FROM ( SELECT TOP 1000 [DealNumber],...others
                  FROM NEW_VIEW
                  ORDER BY x,y,z ) b