Menu
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?
854
The solution to this is very simple. Run both queries using a UNION to combine the results! The UNION operator returns unique records. If the two results sets are identical the row count will remain the same as the original query.
Run the queries and compare logical reads for the various tables and execution times. @CombatCaptain You can also stack the comparing queries together in SSMS and press CTRL+M (include actual execution plan) and then F5 .
In SQL, you can use the = operator to test for equality in a query. In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to Microsoft.
You can use EXCEPT or INTERSECT to compare more than two sets of queries.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
