Red Gate forums :: View topic - Script includes index drop/create when it shouldn't (Bug?)
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
sttu



Joined: 22 Jan 2014
Posts: 13

PostPosted: Wed Jan 29, 2014 5:21 pm    Post subject: Script includes index drop/create when it shouldn't (Bug?) Reply with quote

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?
Back to top
View user's profile Send private message MSN Messenger
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Mon Feb 03, 2014 12:18 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
sttu



Joined: 22 Jan 2014
Posts: 13

PostPosted: Mon Feb 03, 2014 12:55 pm    Post subject: Re: Reply with quote

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!
Back to top
View user's profile Send private message MSN Messenger
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Mon Feb 03, 2014 3:16 pm    Post subject: Reply with quote

I can't comment further without a complete copy of both schemas.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Thu Feb 06, 2014 9:41 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group