Can you add identity to existing column in sql server 2008?

Question

In all my searching I see that you essentially have to copy the existing table to a new table to chance to identity column for pre-2008, does this apply to 2008 also?

thanks.

most concise solution I have found so far:

CREATE TABLE Test 
 ( 
   id int identity(1,1), 
   somecolumn varchar(10) 
 ); 

 INSERT INTO Test VALUES ('Hello'); 
 INSERT INTO Test VALUES ('World'); 


 -- copy the table. use same schema, but no identity 
 CREATE TABLE Test2 
 ( 
   id int NOT NULL, 
   somecolumn varchar(10) 
 ); 

 ALTER TABLE Test SWITCH TO Test2; 

 -- drop the original (now empty) table 
 DROP TABLE Test; 

 -- rename new table to old table's name 
 EXEC sp_rename 'Test2','Test'; 

 -- see same records 
 SELECT * FROM Test; 

Answer 1

In all of the new feature documents I read about 2008, adding identity to an existing column was not a feature I recall. The solution you've found is correct and I think the process of adding identity increment to a column automatically would be only rarely useful.

Answer 2

Well you can do something like this.

ALTER TABLE my_table ADD ID_COLUMN INT IDENTITY (1,1) NOT NULL

Answer 3

You can add the IDENTITY property to an existing column using the GUI of Enterprise Manager / Management Studio.

Answer 4

In SQL 2005 and earlier, you could not modify an existing column to become an identity column. I deem it very very unlikely that MS changed that in 2008.