Request for drop/add vs. table rebuild on computed columns

Jason Hannas · November 24, 2009 1:03PM

It seems to me that computed columns are fairly easily identified. If they are the only things changing in a table synchronization, then why can't they simply be dropped and added (accounting for index and constraint dependencies, of course) rather than forcing a table rebuild?

We make fairly extensive use of computed columns--particularly for accounting systems. I'm not comfortable syncing tables when those column definitions change, because of the full table rebuild which can be quite time consuming on the larger tables.

Drop/add is painless, takes very little time, and works with persisted computed columns in the same manner. It would really save me time when syncing my various environments.

Eddie D · November 26, 2009 8:52AM

Hi Jason

SQL Compare will in most circumstances attempt to alter an existing table when synchronizing a database.

This LINK is to a knowledge base article explains the circumstances as why SQL Compare will rebuild a table.

I hope this article answers your question.

Many Thanks
Eddie

Jason Hannas · December 2, 2009 11:17AM

Thanks for the link--I see (and have now tested) that computed columns are dropped/added in this version of Compare, and may have been available from version 6, for all I know! So that solves 80% of my issue. But I still question why persisted computed columns aren't treated the same way. Drop/Add works just as well for those, too.

Thanks for your reply.
Jason

Eddie D · December 29, 2009 2:53PM

Thank you for your reply and sorry for the delay in replying back to you.

I suspect that SQL Compare treats persisted computed columns as if they are actual table columns. So rather treat them as a special case, it is easier for SQL Compare to perform the rebuild instead of the drop / add.

Many Thanks
Eddie

Request for drop/add vs. table rebuild on computed columns

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk