Adding Not Null with Default column to large Table

From: Neil K (K_at_discussions.microsoft.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 02:49:02 -0700

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!

Can anyone give me any other options to perform this operation ?

I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.

Relevant Pages

  • Re: alter table add column question
    ... > set up code in batches and thoroughly test things before altering their ... > alter table Customer drop column KidsNames ... > alter table Customer add column DogsNames c ...
    (microsoft.public.fox.programmer.exchange)
  • Re: (OT)If Darth Vader were designed by Japanese schoolgirls...
    ... not for those with an aversion to pink... ... "I am altering the hemline. ... Pray I don't alter it any further." ...
    (rec.arts.anime.misc)
  • alter table add column question
    ... set up code in batches and thoroughly test things before altering their ... alter table Customer drop column KidsNames ... alter table Customer add column DogsNames c ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Alter table command vs table copy/rename
    ... Does anyone know what the pros and cons are of doing an alter table ... They were told that altering the table causes a roll ... and that this is extremely slow. ... table statement rather than the copy/rename method. ...
    (microsoft.public.sqlserver.programming)
  • Re: self-aware list of objects able to sense constituent member alterations?
    ... where,  in the midst of a lot of processing I might do something like, ... which alter the properties of the object 'a'. ... Even better would be to automatically percolate the subsequent changes ... that resulted from altering 'a' for the rest of the items in the list. ...
    (comp.lang.python)