作者：桦仔
10余年DBA工作经验
微信：debolop
QQ交流群：740052625
公众号：数据库实战派
简介
在日常的数据库管理和维护中，比较两张表的数据是否一致是非常常见的操作，尤其是在复制发布端和订阅端的场景中。为了保证数据的完整性和一致性，快速准确地找出两张表的差异至关重要。
本文将为你介绍几种在数据库中常用的比较表数据一致性的方法，帮助你在不同场景下选择最合适的解决方案。
注意：下面介绍的几种方法其他数据库也可以使用，例如：MySQL、PostgreSQL，而不局限于SQL Server
SQL Server中如何快速比较两张表的差异
这里我们展示几种从数据库层面解决该问题的常见方法。
第一步：检查记录数
在比较两张表的数据是否一致之前，首先检查两张表的记录数是否相同。如果记录数不同，直接得出不一致的结论即可。
以下是演示如何检查记录数的SQL语句：
-- 两表的记录数都为100条
SELECT COUNT(*) FROM t1_old;
SELECT COUNT(*) FROM 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 '');
第三步：对数据内容进行比较
方法一：使用 UNION 运算符
UNION 可以用来检测两表的差异，但在某些特殊情况下它可能无法准确反映数据是否一致，建议慎用。因为 UNION 会去除重复的行。
SELECT COUNT(*)
FROM (
SELECT * FROM [t1_old]
UNION
SELECT * FROM [t1_new]
) AS T;
两表数据查询结果是4，识别出数据差异
方法二：EXCEPT 减法归零
EXCEPT 是一种可靠的方式，可以有效检测出两张表的差异。这个方法比较直观，简单明了。
减法归零：两个完全相等的数据集 相减 一定等于0
不等于0就是有差异！
-- 查找 t1_new 中存在但 t1_old 中不存在的记录
SELECT COUNT(*)
FROM (
SELECT * FROM [t1_new]
EXCEPT
SELECT * FROM [t1_old]
) AS T;
-- 查找 t1_old 中存在但 t1_new 中不存在的记录
SELECT COUNT(*)
FROM (
SELECT * FROM [t1_old]
EXCEPT
SELECT * FROM [t1_new]
) AS T;
直接能识别出数据差异
方法三：使用 INNER JOIN 进行全表对比
此方法会对整个表进行 INNER JOIN，适用于小数据量的比较场景，但对于数据量大的表，这个方法性能较差。
DECLARE @t1_newcount BIGINT;
DECLARE @count BIGINT;
SELECT @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];
IF (@count = @t1_newcount)
SELECT 'equal';
ELSE
SELECT 'not equal';
方法四：使用 SQL Server 自带的 tablediff 工具
微软提供的 tablediff 工具专门用于比较复制中发布表和订阅表的数据一致性，生成详细的差异报告。这是非常推荐的工具。
方法五：使用发布端的验证订阅功能
SQL Server 的复制功能中自带验证工具，可以检测发布端与订阅端数据是否一致，非常方便。
方法六：CHECKSUM 校验
CHECKSUM 是一种快速比较两张表的方法，但只适用于表结构完全相同的情况下。通过计算表的校验和，来判断数据是否一致。这种方法也是非常消耗性能和时间的方法，对于超大型表，需要谨慎使用。
SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_old];
SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_new];
总结
从以上几种方法来看，使用 EXCEPT 减法归零是相对比较可靠的方法，适用于大部分场景，甚至是大数据量的场景。而 CHECKSUM 和 UNION 则适合特定的场景下快速比较。对于大数据量的场景，可以考虑微软的 tablediff 工具，它能够生成更详细的对比报告。
参考文章
https://medium.com/@sujathamudadla1213/how-do-you-use-the-checksum-function-in-sql-of-sql-server-eb5c8e9b832e
https://blog.csdn.net/u011680118/article/details/52327308
https://www.sqlshack.com/understanding-the-sql-except-statement-with-examples/
https://www.w3schools.com/sql/sql_union.asp
加入我们的微信群，与我们一起探讨数据库技术，以及SQL Server、 MySQL、PostgreSQL、MongoDB、Oracle、Redis 的相关话题。
微信群仅供学习交流使用，没有任何广告或商业活动。
