Two rows look the same until one hidden value changes. HASHBYTES can produce a compact fingerprint, provided you define exactly which bytes go into it.
Choose a Supported HASHBYTES Algorithm
The function takes an algorithm name and a string or binary input. SHA2_256 and SHA2_512 are the current choices for new work. Older algorithms such as MD5 and SHA1 remain for compatibility but are deprecated for new uses. A hash is a fixed-size digest, not a readable summary of the row.
I start by asking what the hash is for. Detecting changed source rows, checking file content, and storing passwords have different requirements. HASHBYTES can help with deterministic comparison, but password storage needs a dedicated salted, slow password hashing scheme outside this simple row fingerprint pattern.
A digest can collide in theory. For change detection, treat a matching hash as a fast signal and keep the original values available for verification when correctness demands it. Do not delete source columns and call the hash complete evidence.
SELECT HASHBYTES('SHA2_256', CONVERT(nvarchar(100), N'example')) AS RowHash;Define the Input Bytes Exactly
The same visible text can hash differently under different encodings or normalization rules. CAST and CONVERT choices matter. So do spaces, case, decimal formatting, and date precision. Define a canonical representation before hashing. A row hash is only stable when every producer builds the same byte sequence.
Concatenating columns with a simple delimiter can create ambiguity. The pair AB and C can produce the same text as A and BC if boundaries are not represented. Use length prefixes, a structured canonical encoding, or an unambiguous format with a tested parser. Handle NULL separately from an empty string.
I test two rows that differ only by NULL versus blank, trailing spaces, or decimal scale. If the hash is the same under a rule that says they differ, the canonicalization is wrong. The algorithm cannot repair missing boundaries.
SELECT HASHBYTES
(
'SHA2_256',
CONCAT(
N'Name:',
CASE WHEN CustomerName IS NULL THEN N'0:'
ELSE CONCAT(N'1:', DATALENGTH(CustomerName),
N':', CustomerName) END,
N'|Code:',
CASE WHEN CustomerCode IS NULL THEN N'0:'
ELSE CONCAT(N'1:', DATALENGTH(CustomerCode),
N':', CustomerCode) END
)
) AS RowHash
FROM dbo.Customer;Use Hashes to Find Changed Rows
A staged source row can carry a hash of the business columns being synchronized. Compare it with the target’s stored hash to find candidate changes. Exclude load timestamps or surrogate keys that change for operational reasons, or every run will appear different.
I keep a clear list of included columns and a hash version. When a new business column is added, the hash definition changes. Existing hashes need recomputation or version-aware comparison. Without that plan, a schema change can look like a mass business update.
The hash narrows the work. A unique business key still matches source to target, and the source values still drive the update. Do not use the hash as a key. A fingerprint tells you whether to look closer, not which customer the row represents.
SELECT s.CustomerCode
FROM dbo.StageCustomer AS s
JOIN dbo.Customer AS t ON t.CustomerCode = s.CustomerCode
WHERE s.RowHash <> t.RowHash
OR s.RowHash IS NULL
OR t.RowHash IS NULL;
Know the HASHBYTES Input Limit by Version
SQL Server 2014 and earlier limit that input to 8,000 bytes. Later versions support larger string or binary input. If an old server is in scope, test the largest expected row encoding rather than discovering the limit during a load.
The hash output is a fixed-length varbinary digest for SHA2 choices. The output size does not tell you how much source text was processed. A wide nvarchar(max) value can be expensive to construct and hash for every row. Measure the cost against simply comparing selected columns.
I avoid guessing that an entire JSON document is a stable row representation. Property order and whitespace can change without changing meaning. Parse the fields that matter, canonicalize them, then hash that contract. The hash should answer a defined business comparison.
SELECT DATALENGTH(CONVERT(nvarchar(max), Notes)) AS InputBytes
FROM dbo.Customer
WHERE Notes IS NOT NULL;Remember That HASHBYTES Is Not Encryption
A hash is one-way in normal use. Encryption is designed so an authorized party can recover the original value with a key. A hash does not protect a low-entropy value from guessing. A phone number or short code can be hashed and still be guessed by hashing likely candidates.
Do not use a plain SHA2 hash as a password store. Password protection needs a unique salt and a purpose-built slow password hashing process. A data pipeline row hash is a different tool. Explain that boundary to anyone who proposes using one digest column for both jobs.
I also review access to the unhashed source values. A hash column does not erase sensitive data in staging or logs. Security controls still apply to the underlying tables and copies.
SELECT HASHBYTES('SHA2_512', CONVERT(varbinary(max), N'example')) AS Digest;Handle NULL and Comparison Semantics
HASHBYTES returns NULL when its input is NULL. A concatenation expression can hide this by replacing NULL with an empty string, but that changes meaning unless explicitly intended. Define a NULL marker that cannot collide with real data or use a structured binary encoding.
Compare hashes using binary values, not formatted hex strings. Store the digest as varbinary of the appropriate length. If the target lacks a hash, treat it as unknown and recompute. A simple inequality does not return true when either side is NULL.
I run a small test matrix for values that business users consider equal and different. The matrix becomes a regression check when the canonicalization code changes. That is more valuable than checking only one happy string.
Measure Before Adding a Hash Column
Hashing every row takes CPU and can add storage and index maintenance. It helps most when comparing many wide columns repeatedly, especially across systems. For a small table, direct column comparison can be simpler and just as fast. Use your workload to decide.
Capture query reads, CPU, and duration for the existing comparison and the hash approach on representative data. Check collision handling and version changes in the operational plan. A faster comparison that silently misses a changed field is no improvement.
HASHBYTES is reliable at hashing the bytes you give it. The hard part is deciding what those bytes mean. Make that contract explicit, preserve source values, and test the edge cases before treating a digest as a change signal.
What exactly is being hashed: a character value, its binary representation, or a serialized row? Document that byte contract before comparing results across systems. Different encodings, collations, and NULL conventions can produce different bytes for values that look identical on screen. A hash match is useful evidence, but it does not remove the need to define canonical input and handle rare collisions when correctness requires an exact comparison.
Related reading on this blog: Introduction to BINARY_CHECKSUM and Working Example and SQL SERVER 2016: Encrypt Your PII Data: Notes from the Field #132.
A hash is not a secret copy of a row, it is a fingerprint of a precisely defined input.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
