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.
/*
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#')
*/
--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'
--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'
------------------------------------------------------------------------------------------------
--create test table
create table table1 (col1 int, col2 varchar(30))
insert into table1 values (100, 'olddata')
--add identity column
alter table table1 add col3 int identity(1,1)
GO
--rename or remove old column
exec sp_rename 'table1.col1', 'oldcol1', 'column'
OR
alter table table1 drop column col1
--rename new column to old column name
exec sp_rename 'table1.col3', 'col1', 'column'
GO
--add new test record and review table
insert into table1 values ( 'newdata')
select * from table1