Requested Improvement for SQL Compare "diff" View

xor88

Please look at this image:

The image shows a "diff" of two similar but not identical index structures on a table. Problems:

As you can see, the indexes 1 and 2 are named differently, so they should ne be compared. They should be listed in different lines.

The statistics 3 and 4 are identical. They should end up in the same line with background color white. There is no difference at all.

5 and 6 are the same again. They just happen to be in a different order.

I propose the following change: Match all objects by name first, then only diff objects that are named exactly the same. Do not let the diff engine do a text diff for all objects because as you can see that technique is really really buggy ;-)

Chris.Allen

Thank you - good points, of course. I'll check to see if this is a known issue and try to get an idea of when it might be addressed.

xor88

Thanks.

Chris.Allen

I chatted with the product experts and they clued me in- the differences that are there and shouldn't be reported are there because what you see is a simple text difference- i.e. this is by design. We do intend to put a little more intelligence in the algorithm but we are trying to gauge if the trade off (in terms of time) is acceptable.

The differences that are not there that are reported are because, when flagging a block of differences, Compare doesn't always notice that there is an identical line- but in a different place. Perfect comparisons are a hard problem (winmerge, 'beyond compare' etc all suffer the same problem ). I believe Compare tries to re-order statements to minimise flagged differences but it cannot do this for all lines.

In summary- this behaviour is all by design but we recognise it could be better and the products continue to evolve.

xor88

Hey Chris,

thanks for the explanation. I fully understand the issue.

I propose a workaround: Match all objects by name into pairs, then let the text diff engine run only on pairs of identically named objects. Then concatenate the individual diffs to one big diff which will now show up correctly. This approach requires only little code changes, certainly no rewrite of the diff engine or the text display component.

This approach also removes the need to reordering, which, as you stated, does not always work in case of insertions and deletions.

Finally, as a motivation to implement this: When using big tables you cannot always just execute the synchronization. Instead you have to cherry-pick the changes and apply only those which can run online or are very fast. Cherry-picking is currently very cumbersome, it would be trivial with a working diff.

xor88

Now that I think about it, this is not a workaround. Imho, this is the optimal solution.