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?
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
The stored proc below will compare the output resultset of 2 stored procedures, or 2 statements. The key here is the SP does not need to know the structure or schema of the result set, thus you can arbitrarily test any SP. It will return 0 rows if the output is the same. This solution uses openrowset command in SQL Server. Here is some sample usage of the Stored proc
DECLARE @SQL_SP1 VARCHAR(MAX)
DECLARE @SQL_SP2 VARCHAR(MAX)
-- Compare results of 2 Stored Procs
SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
-- Compare just 2 SQL Statements
SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
The SP requires the following prerequisites, which may not be ideal for a production environment, but very useful for local QA, DEV and Test environments. It uses openrowset in the code.
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1
EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
Here is the code for the stored proc.
==================================================================================
--== SUMMARY utlCompareStatementResults
--== - requires sp_configure 'show advanced options', 1
--== - requires sp_configure 'ad hoc distributed queries', 1
--== - maybe requires EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
--== - requires the RecordSet Output to have Unique ColumnNames (no duplicate columns)
--== - requires references in straight SQL to be fully qualified [dbname].[schema].[objects] but not within an SP
--== - requires references SP call to be fully qualifed [dbname].[schema].[spname] but not objects with the SP
--== OUTPUT
--== Differences are returned
--== If there is no recordset returned, then theres no differences
--== However if you are comparing 2 empty recordsets, it doesn't mean anything
--== USAGE
--== DECLARE @SQL_SP1 VARCHAR(MAX)
--== DECLARE @SQL_SP2 VARCHAR(MAX)
--== -- Compare just 2 SQL Statements
--== SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
--== SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
--== EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
--==
--== -- Compare results of 2 Stored Procs
--== SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
--== SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
--== EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
--==================================================================================
CREATE PROCEDURE utlCompareStatementResults
@SQL_SP1 VARCHAR(MAX),
@SQL_SP2 VARCHAR(MAX)
AS
BEGIN
DECLARE @TABLE1 VARCHAR(200)
DECLARE @TABLE2 VARCHAR(200)
DECLARE @SQL_OPENROWSET VARCHAR(MAX)
DECLARE @CONNECTION VARCHAR(100)
SET @CONNECTION = 'server='+@@SERVERNAME+';Trusted_Connection=yes'
SET @SQL_SP1 = REPLACE(@SQL_SP1, '''','''''')
SET @SQL_SP2 = REPLACE(@SQL_SP2, '''','''''')
SET @TABLE1 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)
SET @TABLE2 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)
SET @SQL_OPENROWSET =
'SELECT * ' + ' ' +
'INTO ' + @TABLE1 + ' ' +
'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
',''' + @SQL_SP1 +'''); ' +
'SELECT * ' + ' ' +
'INTO ' + @TABLE2 + ' ' +
'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
',''' + @SQL_SP2 +'''); ' +
'(SELECT * FROM ' + @TABLE1 + ' EXCEPT SELECT * FROM ' + @TABLE2 + ') ' +
' UNION ALL ' +
'(SELECT * FROM ' + @TABLE2 + ' EXCEPT SELECT * FROM ' + @TABLE1 + '); ' +
'DROP TABLE ' + @TABLE1 + '; ' +
'DROP TABLE ' + @TABLE2 + '; '
PRINT @SQL_OPENROWSET
EXEC (@SQL_OPENROWSET)
PRINT 'DifferenceCount: ' + CONVERT(VARCHAR(100), @@ROWCOUNT)
END
To complete @jabs answer, you can use the following template to get the difference between two queries:
with q1 as (<INSERT_QUERY_1_HERE>)
, q2 as (<INSERT_QUERY_2_HERE>)
select * from q1 except select * from q2
union all (
select * from q2 except select * from q1);
Example 1: This returns 0 rows, as the queries are identical
with q1 as (select * from my_table)
, q2 as (select * from my_table)
select * from q1 except select * from q2
union all (
select * from q2 except select * from q1);
Example 2: This returns the different rows between the queries (where foo = 'bar')
with q1 as (select * from my_table)
, q2 as (select * from my_table where foo <> 'bar')
select * from q1 except select * from q2
union all (
select * from q2 except select * from q1);
Example 3: Just for fun, you can check that the query in Example 2 is identical to queries the rows where foo = 'bar'.
with q1 as (
with q1 as (select * from my_table)
, q2 as (select * from my_table where foo <> 'bar')
select * from q1 except select * from q2
union all (
select * from q2 except select * from q1)
)
, q2 as (select * from my_table where foo = 'bar')
select * from q1 except select * from q2
union all (
select * from q2 except select * from q1);