How do you alter an existing table int column to identity in MS SQL? Alter command wont work. Here is a simple work around to achieve that.

1. Do a SELECT MAX() on your current table to see where your numbering should start.
SELECT MAX(id) FROM table1

2. Create a new table with a temporary name in the new, permanent format. Be sure you declare the appropriate column as INT IDENTITY.
CREATE TABLE table2 (id INT IDENTITY PRIMARY KEY, foo varchar(20), bar varchar(40))

3. Copy the existing data into the new table. Be sure you have enabled IDENTITY INSERT. The existing numbers should not change, despite their going into an IDENTITY column. Be sure to turn off IDENTITY INSERT when you’re done.

SET IDENTITY_INSERT dbo.table2 ON
INSERT INTO table2 (id, foo, bar)
SELECT id, foo, bar FROM table1
SET IDENTITY_INSERT dbo.table2 OFF

4. Run DBCC to reset the identity value on the table. If you don’t get a new identity value similar to what you saw in step 1, you can force it to a particular value by using the second version below.
DBCC CHECKIDENT (table2, RESEED)
DBCC CHECKIDENT (table2, RESEED, 42)

5. Verify that table2 appears the way you want.

6. DROP TABLE table1

7. Rename table2 to table1
EXEC sp_rename ‘table2′, ‘table1′

Take from our forum post http://www.webcosmoforums.com/databases/19195-alter-table-column-identity-ms-sql.html