Data Compare uses Delete/Insert where Update should suffice

David Lee

Hello,

Using SQL Data compare 6.0 I have just compared a single table on two different servers. 1 row was identified as only appearing in the left DB and 1 row was identified as only appearing in the right DB. On closer inspection the two rows had only a single change in a non-indexed, non-primary key column.

My understanding is that the comparison is performed using the Primary Key as the default (I have checked and that is true in this case).

My query is this: Why did it determine that a DELETE/INSERT pair rather than a single UPDATE statement should be used? What other criteria is used besides the PK?

I hope that this is just my lack of understanding as this nearly caused a catastrophic issue for us as there is a cascade delete relationship on this table that would not be triggered had an UPDATE statement been used.

Thanks

David
Carlson Marketing, Northampton

richardjm

I think that Data Compare will attempt to do this if it determines that any primary key isn't unique during the comparison. If you're using the GUI there should be a warning if this has been detected to be the case. Obviously if you're changing an identity column I think that also causes an insert and delete.

I'm pretty sure those are the only situations where it will use DELETE/INSERT rather than UPDATE.

HTH

David Lee

Thanks for your reply.

I am using the GUI and there is no warning that the index is not unique and there are no identity columns in this table. In fact, it is defined as a CLUSTERED UNIQUE primary key and there are no other indices!

On a test environment I have applied the changes and then reran the comparison and now I have this one row appearing as only in left DB and only in right DB, yet the contents of the rows is the same!

Any ideas?

thanks
David

richardjm

Can I ask what your comparison key is? If it's a string what is the collation of the string? If it is a string perhaps it might be worthwhile trying the option to 'Use Binary Collation'.

If it's not a string then the order in which the rows are being return from each database may differ - in which case I'd be really curious as to why.

David Lee

The comparison key types in order are:
Char( 10 )
Char( 10 )
Varchar( 10 )
Varchar( 10 )
Varchar( 8 )

and the collation is SQL_Latin1_General_CP1_CI_AS across the board.

I will retry the compare with binary collation.

David Lee

I've tried the binary collation and it has successfully identified the recalcitrant row as being the same in both DB. I then rechecked with the binary collation off and the row reappeared as being different.

The key is entirely in Capitals. Why should the case-sensitive comparison identify the two rows as the same whereas the case-insensitive comparison says they're different?

:?

David

richardjm

What can happen in certain circumstances is that the order of strings according to their collations from the database isn't the same as we expect strings to be ordered according to collation.

Sometimes we can get the order wrong in rare cases which is why we've got that lovely option.

I'd be interested to know what the strings are around that area to know the exact problem in our collation implementation as we should be solid and the common collation your using.

David Lee

Richard,

I can send you the table in question directly for analysis purposes but I can't post it on a public site. So if we can arrange it privately I can send it to you.

However, looking at the options again I also tried the compare using the trim trailing spaces option and this had the same effect has the collation option. That is, with the option selected the rows appear as different! I have reviewed data and the two rows in question are identical, including the number of spaces.

I'm not getting any less confused with this! :? :?

David

richardjm

OK now it makes more sense. Bizarrely the ordering changes from SQL Server depending on if there are trailing spaces or not. We don't behave in the same manner so there is an out-of-order problem which you are seeing.

The trim-trailing spaces option is considerably quicker for comparisons than forcing binary collation so I'd stick with that option if it works for you. This is because SQL Server can still use the index when retrieving the data.

If you can send me a copy of the two tables in your servers so I can check that this is the issue and we'll put a test case in and fix it in a future version.

Thanks for all your help on this.