How To Speed Up Adding Column To Large Table In Sql Server

I want to add a column to a Sql Server table with about 10M rows. I think this query would eventually finish adding the column I want:

alter table T
add mycol bit not null default 0

but it's been going for several hours already. Is there any shortcut to get a "not null default 0" column inserted into a large table? Or is this inherently really slow?

This is Sql Server 2000. Later on I have to do something similar on Sql Server 2008.

Solution 1:

Depending on your row size, table size, indexes, etc, I've seen SQL Server 2000 grind away for several hours (4-5ish hours) before FINALLY completing.

The worst thing you can do right now is "panic" and hard kill the thing. Let it run itself out.

In the future, you may wish to try doing what Farseeker mentioned and create a second (empty) structure and copy your records over that way.

• The longer the table row, the longer it will take.
• The more indexes you have on that table, the longer it will take.
• If you add a default value (which you did), it will take longer.
• If you have heavy usage on the server it will take longer.
• If you don't lock that database or put it in single user mode, it will take longer.

When I have to do ugly stuff like this I try and do it at night... like 2am when nobody is on it (and maintentance is NOT running on the server).

Good luck! :-)

Solution 2:

Hmm, 10M rows is a quite few, but it's not outside the realm of MSSQL and that does seem very slow.

We had a table with a huge row size (poorly designed) and over 10M rows. When we had to modify the structure, it was def. very slow, so what we did was (to keep the table online, and this is rough from memory because it was a long time ago):

• Created new table with the suffix "C" (for Conversion) and new structure (i.e. same as old one, but with new column/index/etc)
• SELECT * INTO tableC FROM table
• sp_rename 'table' 'tableOld'
• sp_rename 'tableC' 'table'

This way it doesn't matter how long the conversion takes, as the old data is online. It might cause issues with rows being written to the table whilst the conversion takes place though (this wasn't an issue for us as the data was only written once daily, but queried thousands of times an hour) so you might want to investigate that.