How to get RedGate Data Compare to consider sort order?

Question

I am using SQL-Server 2005.

I have a dev and prod database that have essentially the same data in them. When I do a compare with RedGate SQL Data Compare 5, it says that only 4 records differ. However, when I open up the tables and view them, they are in a completely different sort order. Neither table has an index or anything forcing the sort order, and I'm trying to make sure my dev is sorted in the same order as prod. But RedGate won't tell me when I'm close, because it is apparently finding records that match, even if they aren't in the same sort order. How do I override that?

I'd like to use the tool to tell when I've figured out the sort order, to make sure I have it right.

Answer 1

Differing sort order just means that the rows are placed in the data files in a physically different manner, but the data still matches exactly, so DC still reports that they match. There's no option to get it to "Respect physical disk order" (which is essentially what you're asking), because even if it noticed a difference, there's no way to synchronize the difference, as SQL Server has no method to control of modify disk order.

The order that data is pulled from the disk when you query a table shouldn't ever be relied on - if you require a certain order, you should include an "ORDER BY" clause in your query to force a certain sort order. If there's some reason that you can't use ORDER BY, your only other option to force a certain sorting order is adding a clustered index on the field by which you'd like to sort the table.

If you really need to re-order the data, then you'll need to truncate the table that's "wrong" and populate it with an "INSERT INTO [bad order table] SELECT * FROM [good order table]". Even then, while it should have the desired effect, there's no guarantee.

I'd recommend the order by clause as your best option. If there's a reason none of these options will work, let me know and I can try to suggest something else.