How SQL Compare determine's approach to table changes

SQLScott

Good afternoon,

My question is two-fold. Primarily, when SQL Compare generates a change script and table changes are involved, 9 times out of 10 SQL Compare will create a temporary table, copy the data, then delete the original table. Occassionally we will see it simply use an ALTER TABLE statement.

How does SQL Compare determine which to use?

Is there an option to tell it to NEVER use temporary tables? The reason I ask is because using temporary tables and deleting the original table has SIGNIFICANT ramifications with SQL Replication. We have been using the output scripts from SQL Compare to do our upgrades, but this might need to change if we can't get SQL Compare to stop doing temporary tables.

Thanks...

Scott

JonathanWatts

Scott,

SQL Compare should only use a temporary table to rebuild a table when a change cannot be made via an ALTER statement.

Situations where we use rebuilds are:

• make change an identity value
• add a column in the middle of a table when force column order is enabled
• add/alter/drop PERSISTED computed columns
• when it is not possible to cast one data type to another implicitly
• change filegroups
• change partitioned columns

We have to use a temporary table is the only way to ensure that data is persisted. If SQL Compare is using temporary tables in a situation where an ALTER can be used then we have an issue which we should fix.

We may be able to make changes to SQL Compare in the future to ensure that it can switch the replication to the new table.

Hope this explains our thinking on this matter,

Regards,

Jonathan

SQLScott

Hi Jonathan,

I appreciate your response. I will see if I can come up with some examples where a table was rebuilt when an ALTER probably could have been used.

I'll look into the replication affect a little more so I can provide you with exact informtion and examples.

thanks...

Scott