1

I'm in the process of installing applications and databases.

In order to check whether a file is correct, I can simply take the checksum.
Is there a way to check if a table is correct? (I realise I can drop the entire table into a file and take the checksum of that file, but maybe there's a simpler way.)

4
  • In order to verify a file is correct by checksum, you have to compare the checksum of the current copy of the file to a previously known valid checksum for that file. Do you have an existing copy of the database to compare the table against?
    – J.D.
    May 12 at 11:29
  • @J.D.: yes, that's the whole idea.
    – Dominique
    May 12 at 11:30
  • 1
    What is the origin of the copy that you need to validate it against the original? If you're making the copy via Backup/Restore, backups guarantee consistency so you do not need to check further.
    – AMtwo
    May 12 at 15:09
  • What are you trying to guard against that the backup does not guarantee? May 13 at 4:35

1 Answer 1

2

There are multiple ways and tools out there to do a database comparison. My preference is the tool SQL Examiner Suite. It is simple, powerful, relatively cheap, and can you tell you both schema differences and data differences. It's flexible in the advanced options by allowing you to ignore different things during comparison such as collation differences, defaults, check constraints, etc. And it can compare databases on the same server or across different servers.

Additionally, here's a comprehensive list of tools for database comparison, from MSSQLTips.

If you mostly only care about data differences, don't have a lot of tables to compare, and don't want to spend the money on a tool, you can probably roll your own solution using something like the HASHBYTES() or CHECKSUM() functions. I prefer HASHBYTES() for its higher precision.

Leveraging one of the aforementioned functions, the idea would be to create a single hash per row (of the concatenation of every column in the table), and do a FULL JOIN on the hashes between the two instances of the table, filtering where either side's key is null. This would leave you with just the rows that are different.

How you want to implement the hashing function is your call. If it's a one time comparison, you can do it on the fly (though the join can be slow). If it's a recurring thing, you can add it as a column to the table itself or create a view on top of the table with it as a column, if you're able to alter the schema. This would allow you to create an index on the result of the function (HASHBYTES() is deterministic, I'm not so sure about CHECKSUM()), for better performance during comparison.

The limitation to this method is that both tables need to be accessible to be joined on, either by having both databases on the same server or by leveraging something like a Linked Server to access the other database's server, which potentially can be slow for a lot of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.