SQL Server에서 동일해야 하는 두 쿼리의 결과를 비교합니다.

SQL Server에서 동일해야 하는 두 쿼리의 결과를 비교합니다.

성능을 위해 sql server 2005 저장 프로시저를 약간 수정하고 있으며, 이전 저장 프로시저와 새 저장 프로시저가 정확히 동일한 결과를 반환하는지 확인하고 싶습니다(열이 동일한지, 행이 동일한지 확인하고 싶습니다).

sql server 2005에서 이를 수행하는 간단한 방법이 있습니까?

except 구성을 사용하여 두 쿼리를 일치시킬 수 있습니다.

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

편집:

그런 다음 쿼리를 반대로 실행하여 query2와의 차이점을 찾습니다.

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

@jabs 응답을 완료하려면 다음 템플릿을 사용하여 두 쿼리 간의 차이를 가져올 수 있습니다.추가 열도 추가됩니다(diff_description각 행이 누락된 쿼리를 설명합니다.

with q1 as (
    <INSERT_Q1_HERE>
)
, q2 as (
    <INSERT_Q2_HERE>
)
, missing_from_q2 as (
    select *
    from (
        select * from q1 
        except 
        select * from q2
    )
    cross join (select 'missing from q2' as diff_description)
)
, missing_from_q1 as (
    select *
    from (
        select * from q2 
        except
        select * from q1
    )
    cross join (select 'missing from q1' as diff_description)
)
select * from missing_from_q2
union all
select * from missing_from_q1

아래의 저장 프로시저는 저장 프로시저 2개 또는 문 2개의 출력 결과 세트를 비교합니다.여기서 중요한 것은 SP가 결과 집합의 구조나 스키마를 알 필요가 없으므로 임의로 SP를 테스트할 수 있다는 것입니다.출력이 같은 경우 0 행을 반환합니다.이 솔루션은 SQL Server에서 openrowset 명령을 사용합니다.다음은 저장 프로시저의 몇 가지 사용 예입니다.

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

SP에는 다음과 같은 전제 조건이 필요합니다. 이는 프로덕션 환경에 이상적이지는 않지만 로컬 QA, DEV 및 테스트 환경에 매우 유용합니다.코드에서 열린 행 집합을 사용합니다.

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1
EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE

저장된 프로시저의 코드는 다음과 같습니다.

==================================================================================
    --== 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

create table #OldProcResults (
    <Blah>
)

create table #NewProcResults (
    <Blih>
)

insert into #OldProcResults
    exec MyOldProc

insert into #NewProcResults
    exec MyNewProc

그런 다음 Jabs의 대답을 사용하여 두 표를 비교합니다.

(@jabs가 말한 것처럼) 두 쿼리를 비교하는 열쇠는 EXCUTY입니다.

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

추가 중count(*)각 쿼리에 대해 두 쿼리 모두 동일한 결과가 있는지 확인합니다.혹시라도 다음을 제외하고 삭제되는 행이 반복되는 경우가 있습니다.

각 절차에 대해 하나씩 두 개의 온도 표를 만듭니다.절차를 실행하여 해당 테이블에 행을 삽입합니다.

그런 다음 하나의 MINUS에서 *를 선택하고 다른 하나의 MINUS에서 *를 선택합니다. 비자와 반대의 경우도 마찬가지입니다.

여기 MSSQL에서 문제를 연구하는 동안 도움이 된 접근법을 좀 더 자세히 설명합니다.@tpvasconcelos의 대답은 지금까지 가장 정확합니다.

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

이 쿼리가 T-SQL에서 두 쿼리를 비교하고 결과가 어떤 테이블에서 나오는지 아는 데 가장 잘 작동한다는 것을 발견했습니다.

WITH CTE AS
 (SELECT insert query 1

EXCEPT

SELECT insert query 2)

SELECT insert query 2, 'a'

EXCEPT

SELECT insert query 1, 'a'

UNION ALL

SELECT *, 'b'  FROM cte

언급URL : https://stackoverflow.com/questions/11017678/sql-server-compare-results-of-two-queries-that-should-be-identical