How to alter column to identity(1,1)

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'

 

 

 

 

 

------------------------------------------------------------------------------------------------

 

 

 

Code Snippet

--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