Adding an autonumber to a SQLcolumn which has more than 15 million records

Question

I need to add a autonumber column to an existing table which has about 15 million records in SQL 2005.

Do you think how much time it'll take? What's the better way to do it?

Answer 1

It's really difficult to say how long it will take.

In my opinion, your best bet would be to bring back a copy of the production database, restore it in a development environment, and apply your changes there to see how long it takes.

From there, you can coordinate site downtime, or schedule the update to run when users aren't connected.

Answer 2

To minimize impact, I would create a new table with the identity column added, insert into the new table by selecting from the old table, then drop the old table and rename the new. I'll give a basic outline below. Extra steps may be needed to handle foreign keys, etc.

create table NewTable (
   NewID int identity(1,1),
   Column1 ...
)
go

insert into NewTable
   (Column1, ...)
   select Column1, ...
       from OldTable
go

drop table OldTable
go

exec sp_rename 'NewTable', 'OldTable'
go

Answer 3

Unless it's an emergency:

1. Don't make changes to a live database.
2. Don't make changes to a live database.

To find out how much downtime you'll need, do a restore to a new DB and make the change there.

It shouldn't be very long: it depends not only on how many rows, but even more on how much data there is in each row. (SQL Server is going to copy the entire table over.)

Answer 4

Do you have the option of backing up the production database, applying the changes on another server and changing connection strings? You could even restore it on the same server as the original, change connection strings and get rid of the old database.

May not be feasible if disk space is limited.