Script includes index drop/create when it shouldn't (Bug?)

sttu

Using SQL Compare 10.4.8.87

I have a comparison which shows the differences between the same tables on my development/live installations.

The only differences in the comparison are the names of constraints for numerous columns.

The table indexes are identical - and indeed are not highlighted as different in any way.

The change script, in addition to dropping and creating the constraints as I would expect, also includes drop and create statements for the indexes!!!

This is not the behaviour I would expect, I certainly do not want to be recreating indexes unexpectedly on my production database!

How can I prevent SQL Compare from scripting changes to objects that are unchanged?

Brian Donahue

Hello,
Sometimes an index needs to be dropped in order to facilitate a change to the underlying column. For instance if the column datatype changes or something, you can't alter the table to drop the column until the index based on the column is dropped.

sttu

Brian Donahue wrote:

Hello,
Sometimes an index needs to be dropped in order to facilitate a change to the underlying column. For instance if the column datatype changes or something, you can't alter the table to drop the column until the index based on the column is dropped.

Hi Brian thanks for the reply,
I understand that can sometimes be the case and other objects might need to be re-created depending on the change required.

In the situation I am seeing though, I am able to write drop constraint/create constraints individually without requiring any other modifications to the tables or indexes.

So for a number of tables where I have needed to sync a large number of differences with column constraints I have had to generate the change script then go through and manually edit it to remove all the index drop/creates (and sometimes even a full table rebuild with copy to tmp table and rename) before running it on the target server.

This has been a real pain lately since applying these unnecessary steps cannot be allowed on a production box where the affected tables sometimes have millions of rows, and it's entirely possible to make the required changes without a table or index rebuild - particularly as there are no other differences between the tables other than the constraints!

Brian Donahue

I can't comment further without a complete copy of both schemas.

Brian Donahue

If you can reply to the email we sent from support@red-gate.com with SQL Compare snapshots of the schema, I can find the reason for the index being changed in the script.