Updating nullability of columns in SQL 2008

Question

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?

Answer 1

I think after all I'm just going to go with the N update statements. It's a once-off job to write the script, which is mostly copy-and-paste, and then I can run that script on all applicable databases.

Still, I'd be interested to know if there is another more efficient way of doing this...

Answer 2

DECLARE @command VARCHAR(8000)
DECLARE @index int
SET @index=0
WHILE @index<=N DO BEGIN
 SET @command=@command+'update MyTable SET BitField'+cast(@index as varchar(3))+' = 0 WHERE BitField'+cast(@index as varchar(3))+' IS NULL'
 EXEC(@command)
 SET @index=@index+1
END

and so on...

Answer 3

If you're absolutely sure you want to do this for all the bit columns on the table, you can use a cursor to iterate over those columns and build the script dynamically. This is similar to Alexander's solution, except that it works if the columns aren't numbered 1..n (which I doubt -- I'm guessing those were probably simplified for illustration purposes).

DECLARE @colName sysname;
DECLARE @sql nvarchar(max);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable' AND DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES';

OPEN cur;

FETCH NEXT FROM cur INTO @colName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'UPDATE [MyTable] SET ' + QUOTENAME(@colName)
        + N' = 0 WHERE ' + QUOTENAME(@colName) + N' IS NULL;'
        + N'ALTER TABLE [MyTable] ALTER COLUMN ' + QUOTENAME(@colName)
        + N' bit NOT NULL;'
        + N'ALTER TABLE [MyTable] ADD CONSTRAINT ' + QUOTENAME('DF_' + @colName)
        + N' DEFAULT(0) FOR ' + QUOTENAME(@colName) + N';';

    EXEC (@sql); -- Replace with PRINT @sql; if you want to test first.

    FETCH NEXT FROM cur INTO @colName;
END

CLOSE cur;
DEALLOCATE cur;