How do I modify a primary key column so that it's auto-generated?

Question

Possible Duplicate:
how to set auto increment after creating a table without any data loss?

How do I modify the primary key column (integer type) of a SQL Server 2012 table so that it's auto-generated?

Answer 1

CREATE TABLE dbo.Test(PkCol int CONSTRAINT PK_Test_PkCol PRIMARY KEY, OtherCol varchar(1));

ALTER TABLE dbo.Test
DROP CONSTRAINT PK_Test_PkCol 
GO
ALTER TABLE dbo.Test DROP COLUMN PkCol
ALTER TABLE dbo.Test
ADD PkCol int IDENTITY(1,1) CONSTRAINT PK_Test_PkCol PRIMARY KEY
GO

IDENTITY

Answer 2

You can drop the existing column and add a new column with the column type specified as IDENTITY.

But beware!

I liked and copied the below comment from here:

If you just drop the existing column and add a new column with the same name but define it as identity, you’ll might modify the value of the primary key for some of the records in the table. There is no guarantee that the new column will get the same values as the old columns.

Since there is no way to modify the column itself to be an identity column, you’ll have to create a new table with an identity column, insert the data from the old table into the new table (with set identity_insert on you’ll get the same values in the new column as you had in the old column), rename or drop the original table (It is better to rename it first, so you’ll be able to roll back the modification if something goes wrong), rename the new table to the original table’s name, recreate the foreign keys that referenced the original table and run dbcc checkident to initialize the identity’s seed to the next number.

Answer 3

Since you said SQL 2012, you have another option: sequences. Check out Books Online on how to use "next value for" as a default for a column.