Comparing Two Copies of a Table With CHECKSUM_AGG and HASHBYTES

Matching row counts do not prove that two table copies contain the same data. CHECKSUM_AGG gives a quick difference signal, while stronger per-row hashes and direct comparisons provide better evidence.

Agree on the Comparison Moment

A copy and a live source can disagree because the source changed after copying. Choose a stable comparison moment before selecting a hash function. Use an approved consistent snapshot or a quiet test copy. Avoid a comparison that reads changing tables at unrelated times.

I ask whether both sides represent the same business point before counting anything. Otherwise the audit becomes an efficient search for perfectly legitimate new activity. Also agree on which columns matter, how duplicates are handled, and whether the comparison requires byte equality or business equality.

The temporary examples use positive unique identifiers and deliberately changed teaching data. Run them together in one scratch session. Their differing input values are visible in the setup. The later queries produce your own diagnostic output without claiming measured production counts or timings.

CREATE TABLE #CopyA
(
    CustomerID int NOT NULL PRIMARY KEY CHECK (CustomerID > 0),
    CustomerName nvarchar(100) NULL,
    Amount decimal(12,2) NULL,
    ChangedUtc datetime2(7) NOT NULL
);
CREATE TABLE #CopyB
(
    CustomerID int NOT NULL PRIMARY KEY CHECK (CustomerID > 0),
    CustomerName nvarchar(100) NULL,
    Amount decimal(12,2) NULL,
    ChangedUtc datetime2(7) NOT NULL
);
INSERT #CopyA VALUES
(1, N'Avery', 10.00, '2026-01-02T09:00:00'),
(2, NULL, 20.00, '2026-01-02T10:00:00'),
(1001, N'Casey', 30.00, '2026-01-02T11:00:00');
INSERT #CopyB SELECT CustomerID, CustomerName, Amount, ChangedUtc FROM #CopyA;
UPDATE #CopyB SET Amount = 21.00 WHERE CustomerID = 2;

Count Before Calculating Digests

A count mismatch immediately proves the selected populations differ. A count match only proves equal cardinality. It does not prove matching keys, values, or duplicate distributions. Use COUNT_BIG when the population can exceed an int count.

Specify the same filters on both sides. A full-table source compared with an active-only destination has a predictable mismatch that no hash explains usefully. For real databases, also verify that the selected schemas and types match before treating converted values as comparable.

SELECT 'A' AS CopyLabel, COUNT_BIG(*) AS ComparedRows FROM #CopyA
UNION ALL
SELECT 'B', COUNT_BIG(*) FROM #CopyB;

Use CHECKSUM_AGG as a Quick Signal

BINARY_CHECKSUM compresses the selected row values into an integer. CHECKSUM_AGG combines those integers across the group. Include the key and every relevant comparable column explicitly. A SELECT * definition becomes unstable when schema changes add a column on one side.

The aggregate does not depend on input row ordering, which makes it convenient for a quick comparison. A differing checksum indicates differing selected representations. Matching checksums do not establish equality. Both functions produce compact results with collisions, so distinct data can generate the same signal.

Do not describe a matching CHECKSUM_AGG as a passed migration audit. It is a screening result that tells you where to investigate next. Also verify how your chosen column types participate. A checksum expression that omits unsupported or unselected information cannot compare that information afterward.

SELECT 'A' AS CopyLabel,
    CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CustomerName, Amount, ChangedUtc)) AS QuickChecksum
FROM #CopyA
UNION ALL
SELECT 'B',
    CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CustomerName, Amount, ChangedUtc))
FROM #CopyB;

Group CHECKSUM_AGG by Key Ranges

Group positive identifiers into blocks before reviewing individual rows. This example uses integer division by 1000, so each block represents a predictable range of keys. The count and checksum within each block identify obvious mismatches and narrow the next comparison.

A matching block signal still carries the checksum collision limitation. Do not skip strong validation of matching blocks when complete equality is required. The block view organizes the investigation; it does not turn a weak checksum into a proof.

Which key can locate the same row on both sides? A nonunique key requires a defined duplicate-matching rule first. This sample's primary keys avoid that ambiguity. For composite keys, define the range and row identity from the actual key rather than an invented row number.

;WITH AllRows AS
(
    SELECT 'A' AS CopyLabel, CustomerID, CustomerName, Amount, ChangedUtc FROM #CopyA
    UNION ALL
    SELECT 'B', CustomerID, CustomerName, Amount, ChangedUtc FROM #CopyB
), Blocks AS
(
    SELECT CopyLabel, CustomerID / 1000 AS BlockID,
        COUNT_BIG(*) AS ComparedRows,
        CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CustomerName, Amount, ChangedUtc)) AS QuickChecksum
    FROM AllRows
    GROUP BY CopyLabel, CustomerID / 1000
)
SELECT BlockID,
    MAX(CASE WHEN CopyLabel = 'A' THEN ComparedRows END) AS RowsInA,
    MAX(CASE WHEN CopyLabel = 'B' THEN ComparedRows END) AS RowsInB,
    MAX(CASE WHEN CopyLabel = 'A' THEN QuickChecksum END) AS ChecksumInA,
    MAX(CASE WHEN CopyLabel = 'B' THEN QuickChecksum END) AS ChecksumInB
FROM Blocks
GROUP BY BlockID
ORDER BY BlockID;

Serialize Values Without Ambiguous Concatenation

HASHBYTES with SHA2_256 gives a much stronger row digest. The quality of that comparison still depends on the bytes supplied. Plain concatenation can confuse NULL with an empty string, or make two different field combinations produce the same concatenated text.

The next serialization prefixes text with its byte length and distinguishes null text from present text. Decimal and timestamp values use explicit conversions from matching source types. The fixed field markers and lengths define the input contract. Keep that contract identical on both sides.

Include a large-value string expression at the beginning so concatenation does not truncate a long payload before hashing. This particular teaching schema is small. For wider schemas, specify Unicode, binary values, floating-point representation, and large-object handling deliberately. A stronger digest cannot recover information discarded before it sees the input.

DROP TABLE IF EXISTS #RowHashes;
;WITH AllRows AS
(
    SELECT 'A' AS CopyLabel, CustomerID, CustomerName, Amount, ChangedUtc FROM #CopyA
    UNION ALL
    SELECT 'B', CustomerID, CustomerName, Amount, ChangedUtc FROM #CopyB
)
SELECT CopyLabel, CustomerID,
    HASHBYTES('SHA2_256',
        CONCAT(CAST(N'Name=' AS nvarchar(max)),
            CASE WHEN CustomerName IS NULL THEN N'NULL;'
                ELSE CONCAT(N'TEXT:', DATALENGTH(CustomerName), N':', CustomerName, N';') END,
            N'Amount=', COALESCE(CONVERT(nvarchar(30), Amount), N'NULL'),
            N';ChangedUtc=', CONVERT(nvarchar(33), ChangedUtc, 126), N';')) AS RowDigest
INTO #RowHashes
FROM AllRows;

Match the Digests by the Real Key

Join the digest rows by CustomerID and retain missing rows on either side. The full join makes a missing key visible even when overall counts happen to match. A changed key is a deletion and an insertion under this comparison contract.

SHA2_256 dramatically reduces accidental-collision risk compared with an integer checksum. It is still a digest, not literal equality of all original bytes. When an exact proof is required, compare the selected original values under the agreed semantics too. Hashes are efficient evidence and localization tools, not permission to ignore the data.

;WITH A AS
(
    SELECT CustomerID, RowDigest FROM #RowHashes WHERE CopyLabel = 'A'
), B AS
(
    SELECT CustomerID, RowDigest FROM #RowHashes WHERE CopyLabel = 'B'
)
SELECT COALESCE(a.CustomerID, b.CustomerID) AS CustomerID,
    a.RowDigest AS DigestInA, b.RowDigest AS DigestInB
FROM A AS a
FULL JOIN B AS b ON b.CustomerID = a.CustomerID
WHERE a.CustomerID IS NULL OR b.CustomerID IS NULL OR a.RowDigest <> b.RowDigest
ORDER BY CustomerID;

Inspect the Original Values in the Affected Block

Once a block or key differs, read its original columns side by side. The next query selects the first key block as an example. Replace that input with the affected block from your own output. A digest says that the serialization changed, while these values explain what changed.

I keep null handling and string comparison rules in the final check. SQL collation equality can treat case or trailing spaces differently from a byte-based hash. Decide which meaning the copy contract requires. A cosmetic representation difference and a wrong monetary value need different explanations.

DECLARE @BlockID int = 0;
SELECT COALESCE(a.CustomerID, b.CustomerID) AS CustomerID,
    a.CustomerName AS NameInA, b.CustomerName AS NameInB,
    a.Amount AS AmountInA, b.Amount AS AmountInB,
    a.ChangedUtc AS ChangedInA, b.ChangedUtc AS ChangedInB
FROM #CopyA AS a
FULL JOIN #CopyB AS b ON b.CustomerID = a.CustomerID
WHERE COALESCE(a.CustomerID, b.CustomerID) / 1000 = @BlockID
ORDER BY CustomerID;

Record What Was Compared Beyond CHECKSUM_AGG

Keep the comparison timestamp, source populations, key definition, column list, and serialization version. Include counts and mismatch evidence from the actual run. That lets someone repeat the check without guessing which fields the earlier checksum covered.

For large tables, choose range sizes that fit the investigation and measure the hashing cost. Avoid reading a changing source with inconsistent isolation just to make validation faster. Stable inputs matter more than a neat digest column.

CHECKSUM_AGG starts the conversation cheaply. Canonical row hashes and direct value checks make it stronger. Finish with evidence for the required equality standard, rather than a reassuring number that happened to match.

Related reading on this blog: Comparing Data Between Two Tables and Hashing Data With HASHBYTES.

A matching checksum is not proof of matching tables, it is a signal that still needs the right validation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.