Updating nullability of columns in SQL 2008
- by Shaul
I have a very wide table, containing lots and lots of bit fields. These bit fields were originally set up as nullable. Now we've just made a decision that it doesn't make sense to have them nullable; the value is either Yes or No, default No. In other words, the schema should change from:
create table MyTable(
ID bigint not null,
Name varchar(100) not null,
BitField1 bit null,
BitField2 bit null,
...
BitFieldN bit null
)
to
create table MyTable(
ID bigint not null,
Name varchar(100) not null,
BitField1 bit not null,
BitField2 bit not null,
...
BitFieldN bit not null
)
alter table MyTable add constraint DF_BitField1 default 0 for BitField1
alter table MyTable add constraint DF_BitField2 default 0 for BitField2
alter table MyTable add constraint DF_BitField3 default 0 for BitField3
So I've just gone in through the SQL Management Studio, updating all these fields to non-nullable, default value 0. And guess what - when I try to update it, SQL Mgmt studio internally recreates the table and then tries to reinsert all the data into the new table... including the null values! Which of course generates an error, because it's explicitly trying to insert a null value into a non-nullable column. Aaargh!
Obviously I could run N update statements of the form:
update MyTable set BitField1 = 0 where BitField1 is null
update MyTable set BitField2 = 0 where BitField2 is null
but as I said before, there are n fields out there, and what's more, this change has to propagate out to several identical databases. Very painful to implement manually.
Is there any way to make the table modification just ignore the null values and allow the default rule to kick in when you attempt to insert a null value?
