The row counts match, but the tables still disagree. Comparing data between two tables means checking keys and values, not trusting one reassuring total.
Define Grain and Scope Before Comparing Data Between Two Tables
Before comparing data between two tables, decide what one row means and which columns belong in the comparison. Exclude load timestamps or identity values when they are not business facts. Filter both sides to the same source boundary and time period. A different snapshot can produce legitimate differences.
I check key uniqueness on both tables. A comparison keyed by CustomerId is unreliable if either side has several rows for one customer. Count duplicates first, then decide whether the grain is customer, customer version, or transaction.
Ask whether duplicates matter. EXCEPT returns distinct rows, so it does not report multiplicity differences by itself. A full join on a unique key gives a clearer classification.
SELECT CustomerId, COUNT_BIG(*) AS RowTotal
FROM dbo.CustomerA
GROUP BY CustomerId
HAVING COUNT_BIG(*) > 1;Use EXCEPT in Both Directions for Comparing Data Between Two Tables
EXCEPT returns rows from the left input that do not appear in the right input. Run it in both directions to find missing and extra distinct rows. Align column order and compatible types. An alias name does not fix a swapped column position.
SQL Server treats two NULL values as equal for EXCEPT’s distinct comparison. That is useful for comparing nullable columns. It is different from a simple a.Column = b.Column predicate, where NULL does not equal NULL in a normal WHERE condition.
I keep the two result sets labeled by direction. “A minus B” and “B minus A” are concrete. A single EXCEPT can make one side look clean while the other holds extra rows.
SELECT CustomerId, CustomerName, RegionCode
FROM dbo.CustomerA
EXCEPT
SELECT CustomerId, CustomerName, RegionCode
FROM dbo.CustomerB;Classify Missing Keys With a Full Join
A FULL OUTER JOIN on a unique business key shows keys missing from either side. Use the key’s NULL status only when the key itself cannot be NULL. Otherwise add explicit presence markers in subqueries. A nullable value column cannot distinguish an absent row from a present row with NULL.
I use this view when the next action depends on direction. A missing row in target can need replay. An extra target row can need a deletion rule or an explanation. Do not automatically delete extra rows without knowing whether the source extract is complete.
Keep filters inside each source subquery when they define the compared population. A WHERE condition after the full join can accidentally remove unmatched rows.
SELECT COALESCE(a.CustomerId,b.CustomerId) AS CustomerId,
CASE WHEN a.CustomerId IS NULL THEN 'OnlyB'
WHEN b.CustomerId IS NULL THEN 'OnlyA'
ELSE 'Both' END AS Location
FROM dbo.CustomerA AS a
FULL JOIN dbo.CustomerB AS b ON b.CustomerId=a.CustomerId
WHERE a.CustomerId IS NULL OR b.CustomerId IS NULL;
Compare Nullable Values Explicitly
After matching keys, compare columns under a defined NULL rule. IS DISTINCT FROM is available in recent SQL Server versions and treats NULL differences clearly. For older code, write the NULL cases explicitly. Do not use COALESCE with a magic sentinel that can also be a legitimate value.
I test both NULL, one NULL, equal non-NULL, and different non-NULL. Those cases reveal mistakes quickly. A report that labels two NULL values as different can create noisy false positives.
Normalize case or whitespace only if the business treats those values as equal. Comparing data between two tables should not silently rewrite the rule of equality. Document the collation and type conversion used.
SELECT a.CustomerId, a.CustomerName AS NameA,
b.CustomerName AS NameB
FROM dbo.CustomerA AS a
JOIN dbo.CustomerB AS b ON b.CustomerId=a.CustomerId
WHERE a.CustomerName IS DISTINCT FROM b.CustomerName;Use Hashes as a Candidate Filter
A SHA2 hash of stable business columns can help narrow changes in wide rows. The input serialization must be identical on both sides. Define field order, type conversion, NULL representation, and encoding. A hash difference points to a changed input, but a matching hash is not a replacement for a business key.
I use hashes for large repeated comparisons only after a direct comparison has a known cost. Calculating and storing hashes also costs CPU and storage. When a row is flagged, compare its actual columns to explain the difference.
Do not use CHECKSUM as proof that rows match. Its collision behavior is not suited to a strong equality claim. A robust hash still has theoretical collisions, so retain source values when exact assurance matters.
SELECT CustomerId, RowHash
FROM dbo.CustomerA
EXCEPT
SELECT CustomerId, RowHash
FROM dbo.CustomerB;Reconcile Counts and Totals When Comparing Data Between Two Tables
Counts are a starting signal, not proof. Two missing keys and two extra keys can leave counts equal. Compare key sets and important numeric totals by business grouping. A total can match even when individual values differ, so both levels matter.
I save the comparison’s source boundary and run time. If tables are changing while queries run, each side can represent a different instant. Use a consistent snapshot or a planned quiet period for a high-stakes reconciliation.
Classify differences into missing, extra, changed, and duplicate. That gives the owner a correction path. A flat list of mismatched rows is harder to act on.
SELECT 'A' AS SourceName, COUNT_BIG(*) AS RowsPresent
FROM dbo.CustomerA
UNION ALL
SELECT 'B', COUNT_BIG(*)
FROM dbo.CustomerB;Turn Differences Into Safe Repairs
Do not apply a blind sync from the comparison output. Decide which table is authoritative and whether deletions should propagate. Record each repair under a run ID and test replay. A comparison is evidence. A correction is a separate controlled action.
I rerun both EXCEPT directions and the keyed comparison after repair. Then I inspect a few representative changed values. If differences remain because of expected timing or transformations, document them as intentional.
A trustworthy comparison starts with grain and scope, handles NULLs and duplicates, and uses the right tool for each question. Counts, EXCEPT, full joins, and hashes work together. None should be asked to prove more than it does.
A data difference must be defined at a stable key. If either table contains duplicate keys, a full outer join can multiply rows and make one mismatch look like several. Check uniqueness first. Which columns are part of the comparison, and which are expected to differ, such as load timestamps? I normalize only those fields the business says are equivalent.
For large tables, compare in bounded key ranges so a job can resume after failure. A row hash can narrow candidate differences, but verify changed candidates with exact column comparisons before applying a sync. NULL needs explicit treatment in both the hash serialization and the final predicate. Keep a reconciliation count and a sample of changed keys for review. Synchronization should be an approved write step, not an automatic consequence of finding differences.
Related reading on this blog: SQL Server: Find Distinct Result Sets Using EXCEPT Operator and SQL Server Data Compare Tool.
A matching row count is not matching data, it is one small clue in a full comparison.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
