Recently, I had to add a column to a massive table with lots of indexes in sql server 2000 (~30mil records). The initial code looked like this:
Alter table MyTable Add MyNewColumn char(10) not null default '';
Now, we ran this and it took 32 hours (!) to run. Being that we had to update almost a dozen of these tables, I wasn't too keen on 2 weeks of downtime.
After doing some searching, it turns out that the problem is the 'not null', which means that every row gets given a value for that column, and hence the index gets recalculated for each row. After some research, we came up with these steps:
1 - Create the column as nullable (pretty much instant)
2 - Set the values to the default value, doing only part of the table at a time (takes a while)
3 - Change the column to not null (again, pretty much instant)
After writing some sql to do this, I got it down from 32 hours to 20 minutes. The only tricky bit is to write the sql to update the column to the non-null value - you need to have some well chosen where clause there to divvy the table up into a million records at a time. Best of all, since the update are split into separate chunks, it gives us an indication of progress as opposed to scratching our heads wondering if it'll ever finish. Awesome!
Here's some code for this:
Alter table MyTable Add MyNewColumn char(10) null default '?'; go update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000 go update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000 go update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000 go ..etc.. Alter table MyTable Alter column MyNewColumn char(10) not null;
Thanks for reading! If you found this article helpful, do me a favour and check out my upcoming web-app: ScrumFox agile team management. And if you want to get in touch, I'd love to hear from you: chris.hulbert at gmail.
(Comp Sci, Hons - UTS)
iOS Developer in Sydney.
I have worked at places such as Google, News Corp, Fox Sports, NineMSN, FetchTV, Woolworths, and Westpac, among others. If you're looking for a good iOS developer, drop me a line!