How to alter column to identity(1,1)

I tried

Alter table table1 alter column column1 Identity(1,1);

Alter table table1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1

they all can not work,

any idea about this? thanks

Code Snippet

--create test table

createtable table1 (col1 int, col2 varchar(30))

insertinto table1 values(100,'olddata')

--add identity column

altertable table1 add col3 intidentity(1,1)

GO

--rename or remove old column

execsp_rename'table1.col1','oldcol1','column'

OR

altertable table1 dropcolumn col1

--rename new column to old column name

execsp_rename'table1.col3','col1','column'

GO

--add new test record and review table

insertinto table1 values('newdata')

select*from table1

|||

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

But take spl care when these columns have any constraints / relations.

Code Snippet

/*

For already craeted table Names

Drop table Names

Create table Names

(

ID int,

Name varchar(50)

)

Insert Into Names Values(1,'SQL Server')

Insert Into Names Values(2,'ASP.NET')

Insert Into Names Values(4,'C#')

*/

Code Snippet

--In this Approach you can retain the existing data values on the newly created identity column

CREATE TABLE dbo.Tmp_Names

(

Id int NOT NULL IDENTITY (1, 1),

Name varchar(50) NULL

)ON [PRIMARY]

go

SET IDENTITY_INSERT dbo.Tmp_Names ON

go

IF EXISTS(SELECT * FROM dbo.Names)

INSERT INTO dbo.Tmp_Names (Id, Name)

SELECT Id, Name FROM dbo.Names TABLOCKX

go

SET IDENTITY_INSERT dbo.Tmp_Names OFF

go

DROP TABLE dbo.Names

go

Exec sp_rename 'Tmp_Names', 'Names'

Code Snippet

--In this approach you can’t retain the existing data values on the newly created identity column;

--The identity column will hold the sequence of number

Alter Table Names Add Id_new Int Identity(1,1)

Go

Alter Table Names Drop Column ID

Go

Exec sp_rename 'Names.Id_new', 'ID','Column'

|||

thank you

but I could not change the existing number order and add identity

any idea about this?

|||Nope. There is no way to alter a column to have the identity property. You will have to create a new table and insert into it if you want initial control over the values in the column.