sql-serversql-server-2005

SQL Server compare results of two queries that should be identical

I am modifying a sql server 2005 stored procedure slightly for performance, and I would like to quickly make sure the old stored proc and the new one return the exact same results (the columns are the same, I want to make sure the rows are the same).

Is there a simple way to do this in sql server 2005?

Solution

  • you can use the except construct to match between the two queries.

    select * from (select * from query1) as query1
except
select * from (select * from query2) as query2

    EDIT:

    Then reverse the query to find differences with query2 as the driver:

    select * from (select * from query2) as query2
except
select * from (select * from query1) as query1