Generating MD5 Hash for NVARCHAR and VARCHAR columns across different databases

Question

I am building a utility that generates a single MD5 hash across all CONCATENATED column values for any table row. To make this work, I have to eliminate NULL values using COALESCE and must CAST NUMBER and DATE types to VARCHAR using consistent patterns.

The purpose of this utility is to compare all the data across two different databases as part of a data migration. We wanted to make sure all of our converted ETL code (stored procedures) produced the same results with 100% accuracy after porting it from Netezza to Azure Synapse Analytics.

For the most part, this works extremely well and we were able to identify numerous bugs using this approach but still, this process isn't perfect ... We cannot use this with BLOB columns and we sometimes get slight differences with FLOAT types. But the real headache is how NVARCHAR is hashed differently between Netezza and Azure Synapse Analytics.

Here is an example... First, I will demonstrate what Netezza gives me.

show nz_encoding
-- NOTICE: UTF8

show server_encoding
--  Current server encoding is LATIN9

CREATE TABLE ENCODING_TEST (
    VALUE1  CHARACTER VARYING(255),
    VALUE2  NATIONAL CHARACTER VARYING(255)
);
-- The command completed successfully

INSERT INTO ENCODING_TEST (VALUE1, VALUE2) VALUES('très bien', 'très bien');
-- 1 rows affected

SELECT RAWTOHEX(HASH('très bien', 0)) FROM ENCODING_TEST
-- E5D128AFD34139A261C507DA18B3C558

SELECT RAWTOHEX(HASH(VALUE1, 0)) FROM ENCODING_TEST
-- E5D128AFD34139A261C507DA18B3C558

SELECT RAWTOHEX(HASH(VALUE2, 0)) FROM ENCODING_TEST
-- A54489E883CE7705CDBE1FDAA3AA8DF4
 
SELECT RAWTOHEX(HASH(CAST(VALUE2 AS VARCHAR(255)), 0)) FROM ENCODING_TEST
-- E5D128AFD34139A261C507DA18B3C558

And here is what Azure Synapse Analytics gives me...

CREATE TABLE WZ_BI.ENCODING_TEST (
    VALUE1  CHARACTER VARYING(255),
    VALUE2  NATIONAL CHARACTER VARYING(255)
);
-- Commands completed successfully.

INSERT INTO WZ_BI.ENCODING_TEST (VALUE1, VALUE2) VALUES('très bien', 'très bien');
-- (1 row affected)

SELECT HASHBYTES ('MD5', 'très bien') FROM WZ_BI.ENCODING_TEST
-- 0xE5D128AFD34139A261C507DA18B3C558

SELECT HASHBYTES ('MD5', VALUE1) FROM WZ_BI.ENCODING_TEST
-- 0xE5D128AFD34139A261C507DA18B3C558

SELECT HASHBYTES ('MD5', VALUE2) FROM WZ_BI.ENCODING_TEST
-- 0xC43534A6812499038457EDF545834866

SELECT HASHBYTES ('MD5', CAST(VALUE2 AS VARCHAR(255))) FROM WZ_BI.ENCODING_TEST
-- 0xE5D128AFD34139A261C507DA18B3C558

The question I have is how come the MD5 hash for the NVARCHAR column in Netezza is different than the MD5 hash of the same type and value in Azure Synapse Analytics? I mean it treats the VARCHAR types the same? I really do not want to have to explicitly CAST all NVARCHAR types to VARCHAR to get these to work, but I have not found any other way to make them equivalent.

What am I missing here?

Answer 1

The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character.

Im guessing md5hashing converts everything to its bits and then creates a hash, which would mean its hashing two different values.