Slow progress when adding sequential identity column

We have 8 million row table and we need to add a sequential id column to it. It is used for data warehousing.

From testing, we know that if we remove all the indexes, including the primary key index, adding a new sequential id column was like 10x faster. I still haven't figure out why dropping the indexes would help adding a identity column.

Here is the SQL that add identity column:

ALTER TABLE MyTable ADD MyTableSeqId BIGINT IDENTITY(1,1)

However, the table in question has dependencies, thus I cannot drop the primary key index unless I remove all the FK constraints. As a result adding identity column.

• Is there other ways to improve the speed when adding a identity column, so that client down time is minimal?

or

• Is there a way to add an identity column w开发者_C百科ithout locking the table, so that table can be access, or at least be queried?

The database is SQL Server 2005 Standard Edition.

Adding a new column to a table will acquire a Sch-M (schema modification) lock, which prevents all access to the table for the duration of the operation.

You may get some benefit from switching the database into bulk-logged or simple mode for the duration of the operation, but of course, do so only if you're aware of the effects this will have on your backup / restore strategy.