CODEWITHSUNDEEP
sqlsql-server-2005identity-column
Vikram
Vikram

Reputation: 604

Add an identity column to existing table which is changing always

I have an existing table with 15 million rows in it. I want to add an identity column and make it primary key. The problem is this table is always moving (inserts, updates, deletes). Is it possible to add the identity column with this? Or I have to stop the backgroud processes (it is a tedious task) which updates this table?

Thanks Vikram

Upvotes: 1

Views: 314

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

Given that you have 15 million rows it might take some non-trivial amount of time to execute the ALTER TABLE statement.

Since SQL Server doesn't provide table hints for ALTER TABLE its pretty safe to assume that SQL Server takes a table lock when it executes an ALTER TABLE statement.

During this time no other process will be allowed to Select, insert, update, or delete so you don't have to worry about a race condition with some other process.

If the process takes long enough your other processes will experience timeout errors. Depending on how the processes are written this is either a bad thing or a non-issue, but you'll need to figure that out. If it were me I would turn them off.

Upvotes: 1

Related Questions