I'm attempting to quickly compare large data in two separate applications (one on MySQL, the other on SQL SERVER).
the myData field is a TEXT field in both databases, and I want to see if the value of this field has changed between them (myData can be hundreds of thousands or millions of characters long)
in MySQL:
select sha1(myData) from myTable where mypk=1;
in SQL SERVER:
select right(convert([varchar](45), hashbytes('SHA1', cast(myData as nvarchar(max))), 1),40) from myTable where mypk=1;
My hash output will not match for the same TEXT field value in both DBs. How can I get it to do so?
Here are my assumptions to date:
Additionally, what if instead of SQL Server it was DB2 or Oracle? Is there some simple approach to this (sorry if this part of the q is too vague)?
SQL Server text
is a deprecated type that has been replaced by varchar(max)
for storing long non-unicode strings encoded. So 'varchar(max)' is more likely to be binary-compatible with MySql text than `nvarchar(max)' which uses two bytes per character.