快速驗證兩個表的方法

我們正在做一個 ETL 過程。總而言之，有一堆表應該是相同的。驗證這些表（在兩個不同的伺服器上）實際上是否相同的最快方法是什麼。我說的是模式和數據。

我可以像在單個文件或文件組上那樣對自己的表進行雜湊處理 - 將一個與另一個進行比較。我們有 Red-Gate 數據比較，但由於相關表包含數百萬行，我想要一些性能更高的東西。

一種讓我感興趣的方法是對 union 聲明的創造性使用。但是，如果可能的話，我想進一步探索雜湊的想法。

發布答案更新

對於任何未來的遊客……這是我最終採取的確切方法。它工作得很好，我們在每個數據庫的每個表上都這樣做。感謝下面的答案為我指明了正確的方向。

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

這是我以前做過的事情：

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

它在大約 1,000,000 行的表上執行良好，但我不確定它在超大表上的執行情況如何。

添加：

我已經對我的系統執行了查詢，該系統比較了兩個不同數據庫中的兩個表和 21 個正常類型的欄位，這些數據庫連接到執行 SQL Server 2005 的同一伺服器。該表有大約 300 萬行，大約有 25000 行不同。然而，表上的主鍵很奇怪，因為它是 10 個欄位的複合鍵（它是一個審計表）。

查詢的執行計劃的總成本為 184.25879UNION和 184.22983 UNION ALL。樹成本僅在返回行之前的最後一步（即連接）上有所不同。

實際上執行任一查詢大約需要 42 秒加上大約 3 秒來實際傳輸行。兩個查詢之間的時間是相同的。

第二個補充：

這實際上非常快，每行執行 300 萬行大約需要 2.5 秒：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB

如果這些結果不匹配，您就知道這些表是不同的。但是，如果結果匹配，則**不能保證這些表是相同的，因為

$$ highly unlikely $$校驗和衝突的機會。 我不確定表之間的數據類型更改將如何影響此計算。我會針對system視圖或information_schema視圖執行查詢。

我嘗試對另一個有 500 萬行的表進行查詢，並且該表在大約 5 秒內執行，所以它似乎很大程度上是 O(n)。

引用自：https://dba.stackexchange.com/questions/34356