SQL Compare - changing nullability

jheidt · June 12, 2008 9:43AM

I had to hand tune a sql script made by SQL Compare (v 6.2.0.271), the problem was the nullability of a large group of columns in a large amount of tables.

The problem was the INSERT INTO portion of the code was failing saying that you couldnt insert null values into a non-null field, but there was a default value for the fields that were changed.

ie: IsShared, bit, nullable -> IsShared, bit, not null default 0

Now i hand tuned the scripts by adding IsNull([IsShared],0) to the insert into statements, but this isn't a sustainable solution.

Is there an option I overlooked? I tried a number of different things but no matter what i did the SQL scripts generated were not directly usable.

David Atkinson · June 12, 2008 11:03AM

Hi,

A workable option is to edit the script and set a DEFAULT for your new column, and at the end of the script set the column back to NOT NULL.

However, it's clear that we could provide some help within SQL Compare itself. Could you tell me exactly what you would expect a SQL Compare option to do for you?

Thanks,

David Atkinson
Product Manager
Red Gate Software

jheidt · June 12, 2008 11:44AM

David, the new column does have a default value. The old column did not, and allowed nulls.

The insert statement SQL Compare generates when filling the temp table it creates (which has the default value specified) fails because the insert statement doesnt specify what to do if the value is null - this is per the ANSI SQL spec.

What SQL Compare should do is use IsNull([ChangedColumn],<default value>) in its select statement, or give the user the option to do that.

see here for more of what im talking about:
http://www.developersdex.com/sql/messag ... &r=6164216

jheidt · June 17, 2008 10:42AM

Does anyone have any feedback for me about this? My support contract and licenses for my red gate products are about to expire, and I'm considering my options for what to renew, as it stands here, if I have to manually edit these files or create a tool to do such I don't think SQL compare will work for what my office needs.
Maybe I just overlooked something?

Andras B · June 18, 2008 8:38AM

Well, this is a rather interesting problem. You set up a default value, and no longer allow nulls. But the table itself contains nulls

IMHO null data in such tables should be updated first to the new desired value, which may or may not be the new default value. It is difficult to decide automatically whether the nullability has been changed by mistake, or what the new value for the nullable column should be.

While I would say that doing the data change automatically would corrupt the database, not everyone is as paranoid as I am, and I can see your point that such behaviour could be useful in certain cases. Therefore I've added this feature to SQL Compare. We will use the DRI default with ISNULL if a colum is changed from nullable to not nullable and a DRI default is already set up on the non nullable side, but not on the column that allows nulls. Also, in the first iteration we will not allow automatic reverting to the default value if the datatype has changed in any way. Assuming this feature will not bring unforseen problems in our tests it will be available in the next major release of SQL Compare (SQL Compare 7, scheduled for release in Q3). There will be a high priority warning accompanying such automatic data mangling.

Regards,
Andras

jheidt · June 24, 2008 10:38AM

Andras wrote:

Well, this is a rather interesting problem. You set up a default value, and no longer allow nulls. But the table itself contains nulls IMHO null data in such tables should be updated first to the new desired value, which may or may not be the new default value. It is difficult to decide automatically whether the nullability has been changed by mistake, or what the new value for the nullable column should be.

While I would say that doing the data change automatically would corrupt the database, not everyone is as paranoid as I am, and I can see your point that such behaviour could be useful in certain cases. Therefore I've added this feature to SQL Compare. We will use the DRI default with ISNULL if a colum is changed from nullable to not nullable and a DRI default is already set up on the non nullable side, but not on the column that allows nulls. Also, in the first iteration we will not allow automatic reverting to the default value if the datatype has changed in any way. Assuming this feature will not bring unforseen problems in our tests it will be available in the next major release of SQL Compare (SQL Compare 7, scheduled for release in Q3). There will be a high priority warning accompanying such automatic data mangling.

Regards,
Andras

Indeed, it could cause unintended values to be inserted to the column, a perfect example would be DateUpdated or DateCreated columns with default values, would then get bad data.

I am aware of that situation, but the problem is that we can't use SQL Compare to automate our schema deployments even though thats exactly why we purchased it, and it's a bit frustrating - various versions of our schema exist on our customers side and the nullability thing is really a show stopper.

It's unfortunate, but I guess I'll have to wait for v7. Is there an extensibility model I can plug into and write some code for? This is going to really backburner our planned roll-out, and SQL Compare is pretty much the only game in town, and I dont want to hand-roll the code to avoid this situation. Any suggestions?

Michelle T · June 27, 2008 7:34AM

We should have v7 out Real Soon Now, as long as nothing else trips us up along the way, so hopefully it won't be too awkward to wait for it.

We do have an SDK for SQL Compare / Data Compare - http://www.red-gate.com/products/SQL_Toolkit/ - but I don't think there's any straightforwards way to use it to do what you want.

Eddie D · July 31, 2008 10:42AM

I am pleased to inform you that we have fixed this problem in the recently released SQL Compare V.7.

If you have a valid Support & upgrades option, you can download SQL Compare V.7 using the 'Check for updates' mechanism (SQL Compare GUI ->Help ->Check for updates)

or download using this link: ftp://ftp.red-gate.com/SQLToolbelt.zip

SQL Compare V.7 will install along side any previous versions of the software.

Many Thanks
Eddie

jheidt · August 12, 2008 10:30AM

This is great! I have told the guys here and we will be renewing our license today. Thank you so very much Red Gate, you guys rock!

SQL Compare - changing nullability

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk