Detecting Changed Rows With a Row Hash

A row hash lets you ask “did this row change?” with one comparison instead of one per column. You squeeze the columns you care about into a short fingerprint, store it, and compare fingerprints on the next load. Same fingerprint, same row. Let me build one, then show you the two traps that catch almost everyone.

A nightly feed with five rows

Picture a nightly customer feed. Most rows never change, and you only want to touch the few that did. Below are a target table and a staging table holding tonight’s feed. I planted four differences. Ben moved to Dallas. Chen’s number moved from the mobile column to the phone column. Dara’s name lost its capital letter. Fay is brand new.

DROP TABLE IF EXISTS #Staging;
DROP TABLE IF EXISTS #Target;
CREATE TABLE #Target (CustomerId int PRIMARY KEY, CustomerName nvarchar(30) NOT NULL, City nvarchar(30) NOT NULL,
                      Phone nvarchar(20) NULL, Mobile nvarchar(20) NULL, RowHash varbinary(32) NULL);
CREATE TABLE #Staging (CustomerId int PRIMARY KEY, CustomerName nvarchar(30) NOT NULL, City nvarchar(30) NOT NULL,
                       Phone nvarchar(20) NULL, Mobile nvarchar(20) NULL, RowHash varbinary(32) NULL);
INSERT #Target (CustomerId, CustomerName, City, Phone, Mobile) VALUES
    (1, N'Asha', N'Pune',    N'555-0101', NULL),
    (2, N'Ben',  N'Austin',  N'555-0102', N'555-0202'),
    (3, N'Chen', N'Denver',  NULL,        N'555-0303'),
    (4, N'Dara', N'Boston',  N'555-0104', NULL),
    (5, N'Elan', N'Seattle', N'555-0105', N'555-0205');
INSERT #Staging (CustomerId, CustomerName, City, Phone, Mobile) VALUES
    (1, N'Asha', N'Pune',    N'555-0101', NULL),
    (2, N'Ben',  N'Dallas',  N'555-0102', N'555-0202'),
    (3, N'Chen', N'Denver',  N'555-0303', NULL),
    (4, N'dara', N'Boston',  N'555-0104', NULL),
    (5, N'Elan', N'Seattle', N'555-0105', N'555-0205'),
    (6, N'Fay',  N'Miami',   NULL,        NULL);

Why the plain column compare misses rows

The first instinct is a join with one inequality per column, glued together with OR. Run it and count the rows you get back.

SELECT t.CustomerId, t.CustomerName
FROM #Target AS t
JOIN #Staging AS s ON s.CustomerId = t.CustomerId
WHERE t.CustomerName <> s.CustomerName
   OR t.City <> s.City
   OR t.Phone <> s.Phone
   OR t.Mobile <> s.Mobile
ORDER BY t.CustomerId;

On my server it returns only Ben. Chen is missed because a comparison with NULL is neither true nor false. It is unknown, and WHERE keeps only true. Dara is missed because my server is case insensitive, so Dara equals dara. Fay is missing too, because a join cannot see a row with no partner. You can patch the NULLs with extra tests, but the query grows with every column you add.

Build the row hash, and watch the NULL trap

HASHBYTES with SHA2_256 gives a fingerprint of 32 bytes. CONCAT_WS glues the columns together with a separator, so values cannot run into each other. Here is the catch: CONCAT_WS skips NULL completely. That sounds harmless until a value hops between two nullable columns, which is exactly what Chen’s row did.

SELECT t.CustomerId,
       CASE WHEN HASHBYTES('SHA2_256', CONCAT_WS(N'|', t.CustomerName, t.City, t.Phone, t.Mobile))
               = HASHBYTES('SHA2_256', CONCAT_WS(N'|', s.CustomerName, s.City, s.Phone, s.Mobile))
            THEN 'same' ELSE 'changed' END AS PlainConcat,
       CASE WHEN HASHBYTES('SHA2_256', CONCAT_WS(N'|', t.CustomerName, t.City, ISNULL(t.Phone, N'~null~'), ISNULL(t.Mobile, N'~null~')))
               = HASHBYTES('SHA2_256', CONCAT_WS(N'|', s.CustomerName, s.City, ISNULL(s.Phone, N'~null~'), ISNULL(s.Mobile, N'~null~')))
            THEN 'same' ELSE 'changed' END AS NullMarked
FROM #Target AS t
JOIN #Staging AS s ON s.CustomerId = t.CustomerId
ORDER BY t.CustomerId;

Plain CONCAT_WS says Chen is unchanged, because both sides glue to the same text. Wrapping each nullable column in ISNULL with a marker keeps its place in the string, and Chen shows as changed. Dara shows as changed either way. A hash sees the exact characters, capital D included.

Store the hash, then find the changes

At load time I compute the hash once per row and keep it as a column. After that, change detection is a comparison of two binary values.

UPDATE #Target
SET RowHash = HASHBYTES('SHA2_256', CONCAT_WS(N'|', CustomerName, City, ISNULL(Phone, N'~null~'), ISNULL(Mobile, N'~null~')));
UPDATE #Staging
SET RowHash = HASHBYTES('SHA2_256', CONCAT_WS(N'|', CustomerName, City, ISNULL(Phone, N'~null~'), ISNULL(Mobile, N'~null~')));

SELECT s.CustomerId, s.CustomerName,
       CASE WHEN t.CustomerId IS NULL THEN 'new' ELSE 'changed' END AS WhatHappened,
       DATALENGTH(s.RowHash) AS HashBytes
FROM #Staging AS s
LEFT JOIN #Target AS t ON t.CustomerId = s.CustomerId
WHERE t.CustomerId IS NULL OR t.RowHash <> s.RowHash
ORDER BY s.CustomerId;

Four rows come back: Ben, Chen and Dara as changed, Fay as new. Asha and Elan stay quiet, which is the whole point. Now apply the changes and run the same check again.

UPDATE t
SET CustomerName = s.CustomerName, City = s.City, Phone = s.Phone, Mobile = s.Mobile, RowHash = s.RowHash
FROM #Target AS t
JOIN #Staging AS s ON s.CustomerId = t.CustomerId
WHERE t.RowHash <> s.RowHash;

INSERT #Target (CustomerId, CustomerName, City, Phone, Mobile, RowHash)
SELECT s.CustomerId, s.CustomerName, s.City, s.Phone, s.Mobile, s.RowHash
FROM #Staging AS s
WHERE NOT EXISTS (SELECT 1 FROM #Target AS t WHERE t.CustomerId = s.CustomerId);

SELECT COUNT(*) AS ChangesLeft
FROM #Staging AS s
LEFT JOIN #Target AS t ON t.CustomerId = s.CustomerId
WHERE t.CustomerId IS NULL OR t.RowHash <> s.RowHash;

Three rows updated, one row inserted, and nothing left to change. A second run of the feed would touch zero rows.

Where a row hash bites

The hash is only as careful as its recipe. Keep these four habits.

First, hash only the columns that matter. If a LastLoaded timestamp sneaks in, every row looks changed every night. Second, if you want Dara and dara to count as the same, lower the text before hashing. Third, changing the column list or its order changes every hash, so recompute them all once. Fourth, choose a marker that real data will not contain. Mine is ~null~.

To check this on your own server, run the plain column compare and the hash comparison side by side. Any row where the two disagree is a change your current check is missing.

DROP TABLE IF EXISTS #Staging;
DROP TABLE IF EXISTS #Target;

Next time a feed feels heavy, fingerprint the rows and let the hash do the boring comparing.

A row hash is not magic, it is a fingerprint only as honest as its recipe.

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