2

I am moving my data from SQL Server to MySQL. The data is moving without any issues, but I am trying to come up with a way to validate the integrity of the data between SQL Server and MySQL. I am having issues with regards datatypes VARCHAR and NVARCHAR. Since these are ranging from 255 to MAX. I am trying to do hash comparison between these datatypes but they are not matching.

For testing purpose I tried this:

mysql> SET @test := repeat('t',50000);
Query OK, 0 rows affected (0.00 sec)

mysql> select md5(@test);
+----------------------------------+
| md5(@test)                       |
+----------------------------------+
| a9cf60d59fe2fb94a84bc106eca028be |
+----------------------------------+
1 row in set (0.01 sec)

Trying the same in SQL Server gives something different:

DECLARE @Test NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000);

SELECT HashBytes('MD5', CONVERT(varchar, @Test));

0x3C48C287BC783516AC89297848A104FE

select LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar, @Test)), 2));

3c48c287bc783516ac89297848a104fe

SELECT CONVERT(VARCHAR(32),HashBytes('MD5', @Test),2);

40752EB301B41EEAEB309348CE9711D6

As you can see none of the output in SQL Server is not matching with MySQL. Hence, looking for some guidance here as to how can I do this data comparison.

arsm4
  • 101
  • 2
  • 10

1 Answers1

0

One page as a unit of data storage for sql server database file is only 8KB and the limit for varchar LEN is 8000, so following your test example, you won't be able to get the hex result of 50000 characters per page. Instead, it is the result of your @Test example that is calculated on a string length of 8000 characters.

The Unknown
  • 146
  • 8