How to get RedGate data comparison for sort order consideration?
I am using SQL-Server 2005.
I have a dev and prod database that have essentially the same data in them. When I compare with RedGate SQL Data Compare 5 it says only 4 records differ. However, when I open the tables and view them, they are in a completely different sort order. No table has an index or anything that forces the sort order, and I am trying to make sure my developer sorts in the same order as prod. But RedGate won't tell me when I'm close because it seems to find records that match, even if they're not in the same sort order. How to do it?
I would like to use this tool to tell you when I figured out the sort order to make sure I am correct.
a source to share
A different sort order simply means that the strings are placed in the data files differently, but the data still matches exactly, so the DC still reports that they match. There is no way to make it "respect the physical disk order" (which is essentially what you are asking for), because even if it noticed a difference, there is no way to synchronize the difference since SQL Server has no way to control the disk change.
The order in which data is fetched from disk when querying a table should never be relied upon - if you require a specific ordering, you must include an ORDER BY clause in the query to force a specific sort order.If there is some reason why you cannot use ORDER BY, your only other way to enforce a specific sort order is to add a clustered index on the field you want to sort the table by.
If you really need to reorder the data, you need to truncate the table that is "wrong" and fill it with "INSERT INTO [bad order table] SELECT * FROM [good order table]". Even when it should have the desired effect, there is no guarantee.
I would recommend the order by option as your best option. If there is a reason none of these options will work, please let me know and I may try to suggest something else.
a source to share