SQLSERVER中如何快速比較兩張表的不一樣

標籤：

SQLSERVER中如何快速比較兩張表的不一樣

不知不覺要寫2014年的最後一篇博文了~

 

一般來說，如何檢測兩張表的內容是否一致，體現在複製的時候發布端和訂閱端的兩端的資料上面

我這裡羅列了一些如何從資料庫層面來解決此類問題的方法

第一步當然就是檢查記錄數是否一致，否則不用想其他方法了~
這裡我們用兩張表t1_old，t1_new來示範

方法介紹

 

方法一：老老實實看錶結構和表記錄數，弊端是根本看不到兩張表的資料是否一致，只是看到表結構和記錄數是否一致

--表結構： CREATE TABLE t1_old (  id int NOT NULL,  log_time DATETIME DEFAULT ‘‘) ;
 CREATE TABLE t1_new (  id int NOT NULL,  log_time DATETIME DEFAULT ‘‘) ;--兩表的記錄數都為100條。select count(*) from t1_old;select count(*) from t1_new;

 

 

方法二：加法去重 union 運算子排除重複的，但是有bug，在某些情形下不能簡單表示結果集一致，相當於無效

由於Union 本身具備把上下兩條串連的記錄做唯一性排序，所以這樣檢測起來比較簡單

SELECT  COUNT(*)FROM    ( SELECT    *          FROM      [t1_old]          UNION          SELECT    *          FROM      [t1_new]        ) AS T;INSERT INTO [dbo].[t1_new]        ( [id],[log_time] )VALUES(1,‘‘),(3,‘‘),(4,‘‘)INSERT INTO [dbo].[t1_old]        ( [id],[log_time] )VALUES(1,‘‘),(2,‘‘),(3,‘‘)SELECT * FROM [dbo].[t1_new]SELECT * FROM [dbo].[t1_old]SELECT  COUNT(*)FROM    ( SELECT    *          FROM      [t1_new]          UNION          SELECT    *          FROM      [t1_old]        ) AS T;

兩表資料

查詢出來的結果是4

 

 

方法三：EXCEPT  減法歸零

SELECT  COUNT(*)FROM    ( SELECT    *          FROM      [dbo].[t1_new]          EXCEPT          SELECT    *          FROM      [dbo].[t1_old]        ) AS T;SELECT  COUNT(*)FROM    ( SELECT    *          FROM      [dbo].[t1_old]          EXCEPT          SELECT    *          FROM      [dbo].[t1_new]        ) AS T;SELECT * FROM [dbo].[t1_new]SELECT * FROM [dbo].[t1_old]

這裡檢測出來結果不對，那麼就直接給出不一致的結論

 

 

方法四：用全表INNER JOIN，這個也是最爛的做法，當然這裡指的是在表記錄數超級多的情況下

DECLARE @t1_newcount BIGINTDECLARE @count BIGINTSELECT  @t1_newcount = COUNT(*)FROM    t1_new;SELECT  @count = COUNT(*)FROM    [t1_old] AS a        INNER JOIN [t1_new] AS b ON [b].[id] = [a].[id]                                    AND [b].[log_time] = [a].[log_time] --如果表中還有其他欄位的自行添加PRINT @countPRINT @t1_newcountIF ( @count = @t1_newcount )    BEGIN         SELECT  ‘equal‘    END ELSE    BEGIN        SELECT  ‘not equal‘    END 

 

 

方法五：藉助SQLSERVER內建的tablediff工具，當初微軟製作這個工具的目的就是用於比較複製中發行資料表和訂閱表的資料一致

identical是相等的意思

 

方法六：藉助發布端的驗證訂閱功能，驗證訂閱端跟發布端的資料是否一致

 

 

方法七：用checksum校正，比較兩張表裡的內容的checksum值是否一致

但是這種方法也只局限於兩表結構一摸一樣

我把[t1_new]表的資料複製到一張新的表以便進行比較

SELECT * FROM [dbo].[t1_new]SELECT * FROM [dbo].[t1_newreplica]SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_old]SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_new]SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_newreplica]

總結

從上面幾種資料庫提供的方法來看，用EXCEPT減法來歸零相對來說比較可靠，其他的方法比較適合在特定的情形下來檢測

 

如有不對的地方，歡迎大家拍磚o(∩_∩)o 

SQLSERVER中如何快速比較兩張表的不一樣