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

Forum for users of SQL Compare schema synchronization utility

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

Postby sttu » Wed Jan 29, 2014 5:21 pm

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?
sttu
 
Posts: 13
Joined: Wed Jan 22, 2014 5:39 pm

Postby Brian Donahue » Mon Feb 03, 2014 12:18 pm

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.
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby sttu » Mon Feb 03, 2014 12:55 pm

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!
sttu
 
Posts: 13
Joined: Wed Jan 22, 2014 5:39 pm

Postby Brian Donahue » Mon Feb 03, 2014 3:16 pm

I can't comment further without a complete copy of both schemas.
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Thu Feb 06, 2014 9:41 am

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.
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests