The table must be rebuilt issue

denisgil · September 29, 2011 12:04PM

Hi,

I am changing a primary identity key type from INT to BIGINT. The generated script is rebuilding the table in this case. SQL server 2005 / 2008 works perfectly with an alter table (there no lost of data in this direction).

I am new with your tools. Is there an that there is an option to support this issue ? (Hope to not need to manages it manually !!!)

Also, as a licensed customer, should I use this forum ? Is there a direct support possible (we have support for 1 year). If so, where do we access it ?

James B · October 3, 2011 8:02AM

Thanks for your post.

I've just tried making the same change as you mentioned (from Int to Bigint on the PK column of a table, with no other options) and SQL Compare scripts this as an ALTER.

Can you confirm which version you're on? Also, there are several other reasons why a table will get rebuilt rather than altered, which are detailed in this article - so you may want to check you haven't got the "Force Column Order" option selected, and none of the other possibilities are relevant.

Generally if you want to contact us to look at a specific issue, its best to email support@red-gate.com - the forums are more intended for users to post non urgent issues where other users can also contribute answers. We do get alerted to un-answered posts so we can respond, but this is obviously a slower way to get help!

denisgil · October 3, 2011 8:25AM

Hi,

I have the lattest release of Sql Compare 9.0.0.79 Professional Edition and no update are available. This is the only change I made to the table and the article detailed do not apply.

The only thing that may differ here, is that we are using user defined type.

So we have the following definition:

if exists(select 1 from systypes where name='Identifier')
drop type Identifier
go
create type Identifier
from int
go

if exists(select 1 from systypes where name='Identifier64')
drop type Identifier64
go
create type Identifier64
from bigint
go

Therefore, I change the user defined type for my table declaration and got the table rebuild issue.

Here is a table definition example where I simply change the user defined type:

create table AuditOperationDetails (
AuditOperationDetailID Identifier identity,
AuditOperationID MandatoryFKey not null,
ColumnID MandatoryFKey not null,
PreviousValue sql_variant null
)
go

James B · October 3, 2011 9:05AM

OK, so I've tested with user defined datatypes and now it does do the rebuild, so I'd say that's the cause.

I don't know why this is; i'll need to log it as an issue for the developers to take a look at (if it isn't already).

You shouldn't lose any data with the rebuild; if you check the generated script you'll see it should copy the data into a temporary table first, drop the original, and then rename the temporary back to what it should be called.

denisgil · October 3, 2011 9:22AM

I am not afraid about losing data... I am afraid of processing time for large table. It's fine for one table, I can do it myself this time. Eventually I might have more table to do.

I suggest to have you developers verify more largely the support of user defined type. We are using this feature in a case tool to ease development and to have standards definition across the model (for example: ForeignKey, Amount, Date format (sql 2008), binary masks and so on...

James B · October 3, 2011 9:24AM

Yes, the potential performance hit could be more of an issue. I've logged this under reference SC-5335 - it'll get evaluated by the product manager to get looked at in amongst other priorities, so at this point I cannot give any timeframe unfortunately.

David Atkinson · October 17, 2011 5:20PM

One way of 'fixing' issues like this is to use SQL Compare in conjunction with SQL Source Control. In SQL Compare v9.5 you can override default SQL Compare behavior with your own migration scripts.

For more information visit:

http://www.red-gate.com/MessageBoard/vi ... 1312#51312

David Atkinson
Product Manager
Red Gate Software

denisgil · October 18, 2011 8:17AM

I will try it in TFS but if I am comparing two database, no matter what method I am using, I expect the same result. We are also building our database change script from the command line (in a .cmd script) in order to automate the generation from a case tool to the final script.

I don't understand what is the differences by reading all objects from source control or by reading it in a source or a destination database.

If there are differences, is there a fix that is comming ? The link you provide talk about an early access version, when this version will be availaible and what are the fix include within it ?

Lastly, is there any concern about the early access version for our licences ? Can I install and remove it in a friendly way ?

denisgil · November 24, 2011 1:34PM

Hi,

I never received an answer with the previous post. Would you please do a follow-up or the issue. The most important point is about a fix in order to work with user defined in any use case of comparaison.

I dont want also to 'scrap' my installation, unless you are saying that I can install an early access version safely (implies that I cas removed it too)

David Atkinson · November 24, 2011 2:32PM

Whether you're comparing from source control or from a database, the custom migration scripts should be picked up in SQL Compare's deployment script.

We can't guarantee that pre-release builds will be 100% safe. However, we don't have any reason to believe that the latest builds available for download below will be problematic.

http://www.red-gate.com/products/sql-de ... /migration

We're keen for you to try this out and let us know if it solves your issue. If you need help using the command line, let us know.

Kind regards,

David

The table must be rebuilt issue

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk