SQL Delta Logo
SQL Delta Banner

How to force table re-creation?

Support and information about SQL Delta for SQL Server Version 5

How to force table re-creation?

Postby erhardf » Tue Mar 26, 2013 6:44 am

Hello all,

I would like to force the re-creation of tables, instead of the using the 'alter table' statement, that SQL Delta automatically chooses.
There's a checkbox on the 'Structure options' tab, but this seems to target only views, etc. and appears to have no effect on tables.
Further there is a 'DropAndRecreateTables' option described in the help for the command line, but setting this to 'true' also had no effect in my tests.

Does anybody know how I could persuade SQL Delta to do what I want?

Thanks!
Florian Erhard
erhardf
 
Posts: 2
Joined: Tue Mar 26, 2013 6:34 am
Location: München, Germany

Re: How to force table re-creation?

Postby david » Tue Apr 02, 2013 11:10 pm

Hi,

You could try preserve column order, this is the closest to forcing Delta to drop and create a table. The command line help is incorrect and should not say table.

If a table has data then an alter statement is far more efficient than forcing a re-create which is why we have optimized the table script process.

Is there a reason you want to force a table to be re-created?

Regards
David
david
Site Admin
 
Posts: 423
Joined: Mon Nov 10, 2003 11:22 pm

Re: How to force table re-creation?

Postby erhardf » Wed Apr 03, 2013 8:59 am

Hello David,
thanks for your answer.
I have "preserve column order" always on, but this does not work in my case:

I have a larger database with a lot of varchar tables. Now the task is to migrate that database to unicode = nvarchar data type.

While usually I agree that alter table works best (and I really appreciate that SQL Delta manages this quite well), in this case *for each* table *field* an alter table statement is created. According to a white paper article from Microsoft and also backed by my tests, the alter table approach does not work good for this task:
both, the required time and the required temporary space "explode".

I have no used a snapshot and several text replaces on the xml file in an editor to trick SQL delta into using the create-table method. This is ugly but it works and the result is worth the effort - the migration run now completes with reasonable temp space within two hours.

As SQL Delta already knows both methods, I feel it shouldn't be too hard to add such an option, what do you think?

BR, Florian
erhardf
 
Posts: 2
Joined: Tue Mar 26, 2013 6:34 am
Location: München, Germany

Re: How to force table re-creation?

Postby david » Fri Apr 05, 2013 12:22 am

Hi,

Version 6 internally has the facility to force a table re-create regardless of whether SQL Delta determines a re-create is required however we haven't hooked this to an option.

I'll see if we can add the option.
Regards
David
david
Site Admin
 
Posts: 423
Joined: Mon Nov 10, 2003 11:22 pm


Return to Support Version 5