How to guarantee rows order

Question

I'm trying to implement a methodology to check the integrity of a restored database. It goes as following:

1. Create a snapshot of the database.
2. Gather various metadata:
   1. On disk used space.
   2. Name of tables.
   3. Number of rows in each tables.
   4. ...
3. Generate a cryptographic hash using the data in each table.

My issue goes with the 3rd step to generate the cryptographic hash. I plan to read each row one by one and update my hash with the data they contain. But for my check to match I have to make sure that the row order stays the same.

How can I make sure to always get the same row order, assuming that I don't know upfront the table structure and on what column I can order on.

Answer 1

ORDER BY is the only way to guarantee order. If you have a unique key (primary key, unique index) then order by this, otherwise you'll need to order by all the columns in the table. If you have multiple unique keys and one of them is the clustering key for the table (or the first part of a larger clustering key) then ordering by that would be most efficient. As you don't know ahead of time what table definitions you have you will need to work out what candidate sorting keys you have by checking the contents of sys.indexes and friends.