Changing an existing column to be an identity [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

How to change programmatically non-identity column to identity one?

I want to s开发者_Python百科et a column as identity as I have already created this column in a table.

What syntax do I need? ALTER ... ?

---

You can't add the identity to an existing column, you must create a new column.

---

With a table Test

create table Test(ID int)

You can do this

exec sp_rename 'dbo.Test', 'tmp_Test', 'OBJECT'
go

create table dbo.Test(
  ID int not null identity
)
go

set identity_insert dbo.Test on
go

insert into dbo.Test(ID) select ID from dbo.tmp_Test
go

set identity_insert dbo.Test off
go

drop table dbo.tmp_Test
go

---

IN SMSS right click on your table ->modify. Choose your column ->Identity Specification->Is Identity->Yes.

---

Using SQL you can do this:

ALTER TABLE <TableName> ADD CONSTRAINT PK_<TableName> PRIMARYKEY(Column)