Consistent Hash of Database Table on Different Database Types

Question

I have the same data table on various different database types (Microsoft SQL2005, MySql and IBM Informix, Oracle), and I need to generate a SHA-1 hash result over the data in the table that is the same across the different database types.

I am trying to determine the best way to accomplish this and so far this is what I have come up with:

• Using .NET SqlDataAdapter, select * from desired table into DataTable.
• Sort column names via List.Sort() to ensure consistent ordering between database types
• For each row, perform a hash as follows:
  • Convert each column value to a string and append to rowString, in the order determined above
  • Hash the entire rowString to obtain a rowHash and add this to a list.
• Next, sort all rowHash values by their SHA-1 value and hash the result to obtain the final table hash. This should take care of issues where rows are returned in different order between different database types.

Will I encounter any issues with this implementation? Such as data types from different databases causing different string results?

Also, is sorting necessary? I am assuming that the column and row ordering is not guaranteed unless defined in the query and I want this to be as dynamic as possible.

Thanks.

Answer 1

Watch out for numeric values, in particular with rounding and precision.