CHECKSUM: A Matching Result Does Not Prove Text Identity

CHECKSUM matches do not prove that the complete input text stayed identical. I do not treat a matching checksum as proof that text stayed unchanged. The original values remain necessary for a complete comparison.

Compare actual relationships under an explicit collation

The first pair compares AB with A-B. Their source bytes differ because one contains a hyphen. Under the BIN2 collation in the query, the checksum results differ.

The second pair compares the Unicode text one with negative one. These are text inputs, not typed numeric values. Their measured checksum results also differ under the same explicit BIN2 collation.

The query specifies a binary collation for both checksum expressions. That makes its comparison setting explicit. A binary collation does not turn CHECKSUM into a complete byte-identity test.

No exact checksum numbers are needed for the lesson. The output compares the two computed values directly. That avoids prescribing exact checksum integers while retaining the measured relationship for each supplied pair.

WITH Pairs AS
(
    SELECT CaseId,LeftText,RightText
    FROM (VALUES
        (1,CAST(N'AB' AS nvarchar(10)),CAST(N'A-B' AS nvarchar(10))),
        (2,CAST(N'1' AS nvarchar(10)),CAST(N'-1' AS nvarchar(10))),
        (3,CAST(N'AB' AS nvarchar(10)),CAST(N'AB ' AS nvarchar(10))),
        (4,CAST(N'AB' AS nvarchar(10)),CAST(N'AB' AS nvarchar(10)))
    ) AS v(CaseId,LeftText,RightText)
)
SELECT CaseId,LeftText,RightText,DATALENGTH(LeftText) AS LeftBytes,
    DATALENGTH(RightText) AS RightBytes,
    CASE WHEN CHECKSUM(LeftText COLLATE Latin1_General_100_BIN2)
        =CHECKSUM(RightText COLLATE Latin1_General_100_BIN2) THEN 1 ELSE 0 END AS ChecksumEqual,
    CASE WHEN CONVERT(varbinary(20),LeftText)=CONVERT(varbinary(20),RightText)
        AND DATALENGTH(LeftText)=DATALENGTH(RightText)
        THEN 1 ELSE 0 END AS ExactBytesEqual
FROM Pairs
ORDER BY CaseId;

Read the complete byte witness beside the checksum

The first expected byte counts are four and six. The second counts are two and four. Both hyphen pairs have different complete bytes and different measured checksum results.

ExactBytesEqual compares the converted binary contents and their original byte lengths. Its expected value is zero for both hyphen pairs. That result describes the supplied complete Unicode payloads, rather than the checksum alone.

The binary target is large enough for these short strings. It does not truncate either input before comparison. A shortened binary target would be a different and misleading witness.

I keep the original texts in the output as well. The visible hyphen gives the byte difference a readable explanation. A checksum number by itself cannot show what changed.

Spaces provide another matching checksum example

The third pair adds one trailing ordinary space to AB. Its measured checksum comparison matches. The byte counts differ, and ExactBytesEqual is expected to be zero.

This is a checksum behavior demonstration, not a claim that every application should distinguish trailing spaces. An application’s text equality contract can be different. Name the required contract before selecting a comparison method.

The fourth pair supplies identical text on both sides. Both witness columns are expected to return one. Including that ordinary success prevents the example from presenting every checksum match as an actual difference.

A matching checksum can therefore accompany equal or unequal complete bytes. The result is a screening value with collision possibilities. It is not an identity certificate for the original payload.

Choose a change-detection policy deliberately

If missed changes are unacceptable, a checksum-only decision cannot provide that guarantee. A second comparison can verify candidate matches according to the required equality rule. That rule may concern typed values or exact serialized bytes.

A stronger hash can reduce collision probability but still needs a defined input encoding. Concatenating fields without clear boundaries can introduce another ambiguity. This example concerns one short Unicode value per expression.

I wouldn’t use the result as encryption or an authenticity claim. It does not protect the text from reading or modification. Security needs a separate, suitable design.

The trailing-space pair demonstrates a checksum match with different complete bytes. The identical pair demonstrates a match with identical bytes. Read both witness columns before deciding what a matching checksum means for these inputs.

When adapting the example, retain a known matching pair and a known differing pair. Include changes that your application must detect, such as a sign in numeric text. The business consequence determines whether a missed difference is tolerable.

Use the checksum as a quick screen, and keep the real text close by.

A matching checksum is not complete text identity, it is a result that can hide meaningful differences.

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