How can I change identity property in SQL Server 2008?

Question

Hi there.

I have a table which has no identity column. I want to change a column's identity specification, but SQL Server 2008 doesn't allow that. So, how can I change identity property in SQL Server 2008?

Answer 1

If you want to add a new column as an identity column:

ALTER TABLE [tablename] ADD COLUMN [columnName] int NOT NULL
GO
ALTER TABLE [tablename] ADD PRIMARY KEY ([columnName])

If you're trying to use the SQL 2008 designer, there is a setting you have to disable so that the designer can drop and recreate the table.

Answer 2

under tools-->options-->designers-->table and database designers

uncheck prevent saving changes that require table re-creation