SQL Compare generates ALTER COLUMN statements with NOT NULL

Blue

I have tables that have columns that are defined as "NOT NULL" and that have no default values. Red-gate generates an alter statement with the not null value. This is causing an error because SQL Server does not allow this. In SQL Server it specifies that "Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified". How can I get past this error? Are there any workarounds?
Thanks in advance.

Brian Donahue

Hello there,

The only workaround is to add a default on the new column in the source database. There should be more information about this in the help file under the common issues section.

sparco

Earlier version use to work differently:

- Create a temporary table based on the orginal schema
- Pump data into temporary table
- Drop the orginal table
- Recreate the table with the new column(s)
- Re-populate the newly created table

This gave you a chance to sort out what values should go into the new column. This was an excellent solution.

We purchased the new version which switched to the horrid add column implementation. We stopped using it because it was too time consuming re-coding all the add columns to use the old solution. We were more than happy to wait a few orders of magnitudes more for it do the comparison because we got the final script generated a lot quicker!

It would be great if you could introduce an option that allows you to choose how to deal with adding columns.

Cheers

Craig

Brian Donahue

Hello Craig,

Thanks for your suggestion. We'd changed to the alter table implementation because of the number of complaints that queries were taking too long in the old version. You could still get the new version to work this way, if you inserted new columns in the middle of the table and then used the 'force column order to be identical' option when you run SQL Compare. This would force and old-style table rebuild.

sparco

Thank you for the reply.

I see the reasoning behind the change and the workaround helps but still requires a little bit of effot. It seems that you have the functionality in place within the script engine to do a full rebuild so can you not just add a tick box control the mechanism for all tables. The logic would be:

--Determine If Column Order Changed

ColumnOrderChanged = ColumnOrderChanged OR ForceFullTableRebuild

Selecting per table would be cool but would require more effort etc. The above looks relatively simple to implement.

Cheers

Craig

Brian Donahue

Hi Craig,

You're right about that. It should be super-easy to implement. I'll tag this as a suggestion and hopefully we can get it in there for you.