How do I add the identity property to an existing column in SQL Server

Question

In SQL Server (in my case, 2005) how can I add the identity property to an existing table coulumn using t-sql?

something like:

alter table tblFoo alter column bar identity(1,1)

Answer 1

I don't beleive you can do that. Your best bet is to create a new identity column and copy the data over using an identity insert command (if you indeed want to keep the old values).

Here is a decent article describing the process in detail: http://www.mssqltips.com/tip.asp?tip=1397

Answer 2

Is the table populated? If not drop and recreate the table.

If it is populated what values already exist in the column? If they are values you don't want to keep.

Create a new table as you desire it, load the records from your old table into your new talbe and let the database populate the identity column as normal. Rename your original table and rename the new one to the correct name :).

Finally if the column you wish to make identity currently contains primary key values and is being referenced already by other tables you will need to totally re think if you're sure this is what you want to do :)

Answer 3

alter table tablename alter column columnname add Identity(100,1)

Answer 4

The solution posted by Vikash doesn't work; it produces an "Incorrect syntax" error in SQL Management Studio (2005, as the OP specified). The fact that the "Compact Edition" of SQL Server supports this kind of operation is just a shortcut, because the real process is more like what Robert & JohnFX said--creating a duplicate table, populating the data, renaming the original & new tables appropriately.

If you want to keep the values that already exist in the field that needs to be an identity, you could do something like this:

CREATE TABLE tname2 (etc.)
INSERT INTO tname2 FROM tname1

DROP TABLE tname1
CREATE TABLE tname1 (with IDENTITY specified)

SET IDENTITY_INSERT tname1 ON
INSERT INTO tname1 FROM tname2
SET IDENTITY_INSERT tname1 OFF

DROP tname2

Of course, dropping and re-creating a table (tname1) that is used by live code is NOT recommended! :)