Adding a column to a massive Sql server table

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;

Chris Hulbert

(Comp Sci, Hons - UTS)

Software Developer (Freelancer / Contractor) in Australia.

I have worked at places such as Google, Cochlear, Assembly Payments, News Corp, Fox Sports, NineMSN, FetchTV, Coles, Woolworths, Trust Bank, and Westpac, among others. If you're looking for help developing an iOS app, drop me a line!

Get in touch:
[email protected]
github.com/chrishulbert
linkedin

 Subscribe via RSS