Byte ordering for multibyte characters in SQL Server versus Oracle I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration.

Environment Details:


Oracle 12 - AL32UTF8 character set
Client - NLS_LANG - WE8MSWIN1252
VARCHAR2 field


SQL Server 2016 


Latin1_General_CI_AS collation
NVARCHAR field


I'm using DBMS_CRYPTO.HASH on Oracle to generate a checksum of the whole row, then copying to SQL and using HASHBYTES to generate a checksum of the whole row, which I'm then comparing to validate the data matches.

The checksums match for all rows, except those with multibyte characters.

For example, rows with this character: ◦ do not match in the checksums, even though the data is transferred correctly. When I use DUMP in Oracle or convert to VARBINARY in SQL Server the data matches exactly except for the bytes for this character.

In SQL Server, the bytes are 0xE625 and in Oracle they are 0x25E6.

Why are they ordered differently, and is there a reliable way to convert one to the other to ensure the checksum at the other end matches for strings with multi-byte characters?
What you are seeing is Little-Endian encoding that SQL Server uses to store Unicode characters (more precisely, it uses UCS-2 LE).

More on Little-Endian here: Difference between Big Endian and little Endian Byte order


I don't know how it was possible that 


  When I use DUMP in Oracle or convert to VARBINARY in SQL Server the
  data matches exactly except for the bytes for this character


All the Unicode characters stored in SQL Server, converted to binary, are "inverted", I mean, to see the real codes you should divide them in groups of 2 bytes and invert the order within every pair.

Example:

declare @str varchar(3) = 'abc';
declare @str_n nvarchar(3) = N'abc';

select cast(@str as varbinary(3));
select cast(@str_n as varbinary(6));


The result is 

0x616263

0x610062006300


As you see in case of Unicode characters bytes are inverted:
"a" is represented as 0x6100 and not as 0x0061.

The same story is about 0x25E6 that is real Unicode code while in binary representation in SQL Server you see it as 0xE625, i.e. inverted.