Red Gate forums :: View topic - The table must be rebuilt issue
Return to www.red-gate.com RSS Feed Available

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

The table must be rebuilt issue

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
denisgil



Joined: 29 Sep 2011
Posts: 6

PostPosted: Thu Sep 29, 2011 5:04 pm    Post subject: The table must be rebuilt issue Reply with quote

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



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Oct 03, 2011 1:02 pm    Post subject: Reply with quote

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



Joined: 29 Sep 2011
Posts: 6

PostPosted: Mon Oct 03, 2011 1:25 pm    Post subject: Reply with quote

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
_________________
Denis Gilbert
Conseiller-Adjoint en TI,
Développement de systèmes
Morneau Shepell Ltée
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Oct 03, 2011 2:05 pm    Post subject: Reply with quote

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



Joined: 29 Sep 2011
Posts: 6

PostPosted: Mon Oct 03, 2011 2:22 pm    Post subject: Reply with quote

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...
_________________
Denis Gilbert
Conseiller-Adjoint en TI,
Développement de systèmes
Morneau Shepell Ltée
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Oct 03, 2011 2:24 pm    Post subject: Reply with quote

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



Joined: 05 Dec 2005
Posts: 1122
Location: Twitter: @dtabase

PostPosted: Mon Oct 17, 2011 10:20 pm    Post subject: Reply with quote

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/viewtopic.php?p=51312#51312

David Atkinson
Product Manager
Red Gate Software
Back to top
View user's profile Send private message Send e-mail
denisgil



Joined: 29 Sep 2011
Posts: 6

PostPosted: Tue Oct 18, 2011 1:17 pm    Post subject: Reply with quote

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 ?
_________________
Denis Gilbert
Conseiller-Adjoint en TI,
Développement de systèmes
Morneau Shepell Ltée
Back to top
View user's profile Send private message
denisgil



Joined: 29 Sep 2011
Posts: 6

PostPosted: Thu Nov 24, 2011 7:34 pm    Post subject: Last post Reply with quote

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)
_________________
Denis Gilbert
Conseiller-Adjoint en TI,
Développement de systèmes
Morneau Shepell Ltée
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1122
Location: Twitter: @dtabase

PostPosted: Thu Nov 24, 2011 8:32 pm    Post subject: Reply with quote

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-development/sql-source-control/entrypage/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
Back to top
View user's profile Send private message Send e-mail
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