Szybki sposób na sprawdzenie dwóch tabel względem siebie

Wykonujemy proces ETL. Kiedy wszystko jest już powiedziane i zrobione, jest kilka tabel, które powinny być identyczne. Jaki jest najszybszy sposób sprawdzenia, czy te tabele (na dwóch różnych serwerach) są w rzeczywistości identyczne. Mówię zarówno o schemacie, jak i danych.

Czy mogę zrobić skrót na stole, to jest tak, jakbym był w stanie dla pojedynczego pliku lub grupy plików - aby porównać jeden. Porównujemy dane Red-Gate, ale ponieważ tabele, o których mowa, zawierają miliony wierszy, chciałbym coś bardziej wydajnego.

Intryguje mnie jedno podejście do twórczego wykorzystania oświadczenia związku . Ale chciałbym zbadać pomysł skrótu, jeśli to możliwe.

AKTUALIZACJA ODPOWIEDZI PO

Dla każdego przyszłego vistora ... oto dokładne podejście, które ostatecznie wybrałem. Działało tak dobrze, że robimy to na każdym stole w każdej bazie danych. Dzięki odpowiedziom poniżej za wskazanie mi właściwego kierunku.

CREATE PROCEDURE [dbo].[usp_DatabaseValidation]
    @TableName varchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    -- parameter = if no table name was passed do them all, otherwise just check the one

    -- create a temp table that lists all tables in target database

    CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int);
    INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM MyDatabase.sys.tables T
            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
        WHERE 
            T.name like IsNull(@TableName,'%');

    -- create a temp table that lists all tables in source database

    CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int)
    INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyLinkedServer].[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM [MyLinkedServer].[MyDatabase].sys.tables T
            INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON 
            T.schema_id = S.schema_id
        WHERE
            T.name like IsNull(@TableName,'%');;

    -- build a dynamic sql statement to populate temp tables with the checksums of each table

    DECLARE @TargetStmt VARCHAR(MAX)
    SELECT  @TargetStmt = COALESCE(@TargetStmt + ';', '')
            + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + T.FullName + ') WHERE [name] = ''' + T.Name + ''''
    FROM    #ChkSumTargetTables T

    SELECT  @TargetStmt

    DECLARE @SourceStmt VARCHAR(MAX)
    SELECT  @SourceStmt = COALESCE(@SourceStmt + ';', '')
            + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + S.FullName + ') WHERE [name] = ''' + S.Name + ''''
    FROM    #ChkSumSourceTables S

    -- execute dynamic statements - populate temp tables with checksums

    EXEC (@TargetStmt);
    EXEC (@SourceStmt);

    --compare the two databases to find any checksums that are different

    SELECT  TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH]
    FROM #ChkSumTargetTables TT
    LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name
    WHERE IsNull(ST.chksum,0) <> IsNull(TT.chksum,0)

    --drop the temp tables from the tempdb

    DROP TABLE #ChkSumTargetTables;
    DROP TABLE #ChkSumSourceTables;

END

Oto co zrobiłem wcześniej:

(SELECT 'TableA', * FROM TableA
EXCEPT
SELECT 'TableA', * FROM TableB)
UNION ALL
(SELECT 'TableB', * FROM TableB
EXCEPT
SELECT 'TableB', * FROM TableA)

Działa wystarczająco dobrze na stołach, które mają około 1 000 000 wierszy, ale nie jestem pewien, jak dobrze by to działało na bardzo dużych stołach.

Dodany:

Uruchomiłem zapytanie w moim systemie, który porównuje dwie tabele z 21 polami regularnych typów w dwóch różnych bazach danych podłączonych do tego samego serwera z programem SQL Server 2005. Tabela ma około 3 miliony wierszy i jest około 25000 wierszy różnych. Klucz podstawowy w tabeli jest jednak dziwny, ponieważ jest to złożony klucz składający się z 10 pól (jest to tabela audytu).

Plany wykonania zapytań mają łączny koszt 184.25879 dla UNIONi 184.22983 dla UNION ALL. Koszt drzewa różni się tylko w ostatnim kroku przed zwróceniem wierszy, konkatenacji.

Właściwie wykonanie któregokolwiek z zapytań zajmuje około 42 sekund plus około 3 sekund, aby faktycznie wysłać wiersze. Czas między dwoma zapytaniami jest identyczny.

Drugi dodatek:

Jest to naprawdę wyjątkowo szybkie, każdy z nich działa na 3 milionach wierszy w około 2,5 s:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB

Jeśli wyniki nie są zgodne, wiesz, że tabele są różne. Jeśli jednak wyniki są zgodne, nie ma gwarancji, że tabele są identyczne z powodu [bardzo mało prawdopodobnej] szansy na kolizję sumy kontrolnej.

Nie jestem pewien, jak zmiany typu danych między tabelami wpłynęłyby na to obliczenie. Uruchomiłbym zapytanie względem systemwidoków lub information_schemawidoków.

Próbowałem zapytania w stosunku do innej tabeli z 5 milionami wierszy, która działała około 5 sekund, więc wydaje się, że jest to w dużej mierze O (n).

Oto kilka pomysłów, które mogą pomóc:

1. Wypróbuj inne narzędzie do porównywania danych - czy wypróbowałeś zestaw narzędzi Idera SQL Compare lub ApexSQL Data Diff . Zdaję sobie sprawę, że już zapłaciłeś za RG, ale nadal możesz użyć ich w trybie próbnym, aby wykonać zadanie;).

2. Dziel i zdobywaj - co powiesz na dzielenie tabel na 10 mniejszych tabel, które mogą być obsługiwane przez jakieś komercyjne narzędzie do porównywania danych?

3. Ogranicz się tylko do niektórych kolumn - czy naprawdę musisz porównywać dane we wszystkich kolumnach?

Uważam, że powinieneś zbadać BINARY_CHECKSUM, chociaż wybrałbym narzędzie Czerwonej Bramy:

http://msdn.microsoft.com/en-us/library/ms173784.aspx

Coś takiego:

SELECT BINARY_CHECKSUM(*) from myTable;

Jeśli masz klucz podstawowy, jest to czasem lepszy sposób na sprawdzenie różnic, ponieważ wiersze, które powinny być takie same, są wyświetlane razem.

SELECT
   ID = IsNull(A.ID, B.ID),
   AValue = A.Value,
   BValue = B.Value
FROM
   dbo.TableA A
   FULL JOIN dbo.TableB B
      ON A.ID = B.ID
WHERE
   EXISTS (
      SELECT A.*
      EXCEPT SELECT B.*
   );

Zobacz to w sqlfiddle .