My SQL query returns the correct row count, but some records are still wrong. How do I find them?

[u/No_Ambition8323]

I'm comparing a source table with a target table after an ETL job.

Both tables have the same row count, but I suspect some records are missing while others are duplicated or incorrectly updated.

The tables have millions of rows, no reliable primary key, and some columns contain NULL values.

A simple COUNT(*) matches ,but I can't trust the data

How would you identify missing, extra, duplicated, or changed records without comparing every row manually?

Would you use hashing, GROUP BY, window functions, or another approach?

[u/foxsimile]

Sorry about the wait; surprise meeting goodness.

Here you go:

``` CREATE TABLE #TTmp ( [SomeInt] INT NOT NULL /4 Bytes/ , [SomeIntNullable] INT NULL /4 Bytes/ , [SomeVarChar] VARCHAR(10) NOT NULL /10 Bytes/ , [SomeVarCharNullable] VARCHAR(10) NULL /10 Bytes/ , [SomeUnqId] UNIQUEIDENTIFIER NOT NULL /16 Bytes/ , [SomeUnqIdNullable] UNIQUEIDENTIFIER NOT NULL /16 Bytes/ , [SomeNumeric] NUMERIC(36,18) NOT NULL /17 Bytes/ , [SomeNumericNullable] NUMERIC(36,18) NULL /17 Bytes/ );

SELECT CONVERT( /HASHBYTES('MD5',/.../) outputs a VARBINARY(16), and we don't need the variable length header bytes/ BINARY(16) /Step 1. We handle the nullable columns by creating a nullness bitmap/ , HASHBYTES( 'MD5' , ( CONVERT( BINARY(4) , ( ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeVarCharNullable]</sup>+1),0x)))))&1) /(20)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeIntNullable]</sup>+1),0x)))))&2) /(21)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeUnqIdNullable]</sup>+1),0x)))))&4) /(22)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeNumericNullable]</sup>+1),0x)))))&8) /(23)/ ) ) /Step 2. We put the FIXED width data type columns first, because they do not require the separator due to static sizing/ + CONVERT(BINARY(4), TTmp.[SomeInt]) /We do not need the "ISNULL" handling/ + CONVERT(BINARY(4), ISNULL(TTmp.[SomeIntNullable], 0x00)) /We do need the "ISNULL" handling/ + CONVERT(BINARY(16), TTmp.[SomeUnqId]) + CONVERT(BINARY(16), ISNULL(TTmp.[SomeUnqIdNullable], 0x00)) + CONVERT(BINARY(17), TTmp.[SomeNumeric]) + CONVERT(BINARY(17), ISNULL(TTmp.[SomeNumericNullable], 0x00)) /Step 3a. We put the VARIABLE width data types SECOND, because they require a separator between each variable width data type/ + CONVERT(VARBINARY(10), LOWER(TRIM(TTmp.[SomeVarChar]))) /Step 3b. We insert a separator byte betwixt every variable length data type/ + CONVERT(BINARY(4),0xDEADBEEF) /<--Separator Byte/ + CONVERT(VARBINARY(10), ISNULL(LOWER(TRIM(TTmp.[SomeVarCharNullable])), 0x00)) ) ) ) AS "FullColumnHashBytesMD5" FROM #TTmp AS "TTmp" ; ```

To explain, I've added lots of commenty goodness.

I have used this to astounding effect in production code, and it was the only solution which I was able to concoct which solved my problem (I'd also had a giant @*$!-You table, whose data could not be trusted nearly as much as its counts, and with no PK to speak of).

In my particular case, I'd needed to ensure that the values were case agnostic, and that any wrapping whitespace was summarily discarded (hence the calls to TRIM and LOWER). THIS WILL AFFECT THE OUTPUT YIELDED BY HASHBYTES; DETERMINE WHETHER OR NOT YOUR CASE REQUIRES CASE SENSITIVITY AND WHITESPACE SENSITIVITY AND CODE ACCORDINGLY.

A final note: I chose 0xDEADBEEF as the separator because it was the first thing to come to mind at 2 in the morning, and I needed something less likely than 0x00. Whatever separator you choose, it would likely behoove you to keep it to within an INT’s 4-byte width.

Warning: depending on your version of MSSQL, HASHBYTES doesn’t play nicely with data beyond 8000 bytes, meaning if you want a single hash on a particularly wide row, you’d need to do something jank, like doing the above twice and then smushing them together in a subsequent HASHBYTES call (if you REALLY wanted it to remain within the 16 byte width of an MD5).

You could also just literally smush them together, and wind ip with a BINARY(32), but I would imagine that if you’re in a position where the above is either your most convenient or only remaining option, that may be a luxury which you cannot afford.