Can you add identity to existing column in sql server 2008?
- by bmutch
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;
