How to add identity to the column in SQL Server?

Question

I have a table about 10 million rows. We just imported it from another database with SQL Server Management Studio. It creates table but without identity and primary key on primary key column.

I could add the primary key but couldn't add identity. It's all the time timing out when I'm doing it in designer. Even I set time out settings to 0.

I need to create probably another column set primary key and identity, copy data from old, delete old column and rename new one.

Can anyone show me what will be the best way to do it for such big tables, without additional overheating?

Answer 1

You cannot add IDENTITY to an existing column. It just cannot be done.

You'll need to create a new column of type INT IDENTITY and then drop the old column you don't need anymore (and possibly rename the new column to the old name - if that's needed)

Also: I would not do this in the visual designer - this will try to recreate the table with the new structure, copy over all data (all 10 millions rows), and then drop the old table.

It's much more efficient to use straight T-SQL statements - this will do an "in-place" update, non-destructive (no data is lost), and it doesn't need to copy around 10 millions rows in the process...

ALTER TABLE dbo.YourTable
  ADD NewID INT IDENTITY(1,1) NOT NULL

When you add a new column of type INT IDENTITY to your table, then it will be automatically populated with consecutive numbers. You cannot stop this from happening, and you also cannot update the values later on.

Neither of those options is really very useful, in the end - you might end up with different ID values.... to do this right, you'd have to:

• create the new table ahead of time, with the proper structure and the IDENTITY already in place
• then turn on SET IDENTITY_INSERT (yourtable) ON on that table to allow values to be inserted into the identity column
• copy over this data from the original source
• turn off identity insert again: SET IDENTITY_INSERT (yourtable) OFF

Only with this approach will you be able to get the same ID's in an IDENTITY column in your new table.