SQL Server compare results of two queries that should be identical

Sql ServerSql Server-2005

Sql Server Problem Overview

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?

Sql Server Solutions

Solution 1 - Sql Server

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

Solution 2 - Sql Server

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

Solution 3 - Sql Server

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

Solution 4 - Sql Server

create table #OldProcResults (
	<Blah>
)

create table #NewProcResults (
	<Blih>
)

insert into #OldProcResults
	exec MyOldProc
	
insert into #NewProcResults
	exec MyNewProc

then use Jabs' answer to compare the two tables.

Solution 5 - Sql Server

EXCEPT is the key to compare two querys (as @jabs said).

SELECT count(*), * FROM "query 1 here"
EXCEPT
SELECT count(*), * FROM "query 2 here"

Adding count(*) for each query to make sure both have the same results. Just in case there are some repeated rows which are deleted by except.

Solution 6 - Sql Server

create two temp tables, one for each procedure. run the procedure to insert rows into the appropriate table.

then select * from one MINUS select * from the other and visa-versa

Solution 7 - Sql Server

Here some more verbose aproach that helped me while studying the matter on MSSQL. @tpvasconcelos´s answer is just the most correct so far.

DECLARE @AAA TABLE(id bigint NOT NULL) 
	INSERT INTO @AAA
	VALUES (1),(2),(3),(4),(5),(6),(7)

DECLARE @bbb TABLE(id bigint NOT NULL)
	INSERT INTO @bbb
	VALUES (1),(2),(3),(4),(5),(6),(7)

Declare @diff int = (SELECT COUNT(*) FROM (SELECT * FROM @AAA EXCEPT SELECT * FROM @bbb) AS TB)


Declare @aux1 int;
set @aux1 = (select count(*) from @BBB);

Declare @aux2 int;
set @aux2 = (SELECT COUNT(*)  FROM @AAA)

Declare @aux3 int;
set @aux3 = (SELECT COUNT(*) FROM (select * from @AAA union SELECT * FROM @bbb) as tb);  -- for union to work it needs a alias


IF @diff <> 0
	begin
		PRINT 'Flow @flows_name has failed.'
	end
else
	begin
		IF @aux1 = @aux3
			begin
				PRINT 'Flow @flows_name might have SUCCEEDED!'
			end
		else
			begin
				PRINT 'Flow @flows_name has failed.'
			end
	end
Categories
Recommended Sql Server Solutions
Recommended Sql Server-2005 Solutions

Previous Solution:

How can I force a query to not use a index on a given table?

SqlSql Server-2005Indexing

Next Solution:

Using the "alternate screen" in a bash script

LinuxBashTerminalTerminfo

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionOxed FrederikView Question on Stackoverflow
Solution 1 - Sql ServerjabsView Answer on Stackoverflow
Solution 2 - Sql ServertpvasconcelosView Answer on Stackoverflow
Solution 3 - Sql ServerCodeCowboyOrgView Answer on Stackoverflow
Solution 4 - Sql ServerDavid BrabantView Answer on Stackoverflow
Solution 5 - Sql ServerAntonio RodríguezView Answer on Stackoverflow
Solution 6 - Sql ServerRandyView Answer on Stackoverflow
Solution 7 - Sql ServerJoão Pedro GonçalvesView Answer on Stackoverflow