Hi all,

         I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

Can anyone please assist me to do such a task?

        Thanks in advance!

I assume, you do not have any constraint and index on ID column of the table.

Try the below:

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

Insert into IdentityTest Select 1,'test',GETDATE()
Insert into IdentityTest Select 10,'test',GETDATE()

ALTER TABLE dbo.IdentityTest Add Id1 int Identity(1,1)

ALTER TABLE dbo.IdentityTest DROP COLUMN id

EXEC sp_rename 'IdentityTest.Id1' ,'id','COLUMN'

Select * From dbo.IdentityTest

Drop table identityTest

Also , few more alternatives explained here http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

Hi SathyJay,

if you want to keep the ID's that are already in the table, you can use the following solution:

create table dbo.IdentityTest (
	id int not null,
	descript varchar(255) null,
	T_date datetime not null
)

Insert into IdentityTest Select 1,'test',GETDATE()
Insert into IdentityTest Select 10,'test',GETDATE()

CREATE TABLE dbo.IdentityTest_TEMP (
	id int Identity(1,1) not null,
	descript varchar(255) null,
	T_date datetime not null
)

SET IDENTITY_INSERT dbo.IdentityTest_TEMP ON

INSERT INTO dbo.IdentityTest_TEMP(id, descript, T_date)
SELECT * FROM dbo.IdentityTest

SET IDENTITY_INSERT dbo.IdentityTest_TEMP OFF

DROP TABLE dbo.IdentityTest
EXEC sp_rename 'IdentityTest_TEMP' ,'IdentityTest'

INSERT INTO dbo.IdentityTest(descript, T_date) VALUES('Test', getdate())

Select * From dbo.IdentityTest

DROP TABLE IdentityTest

Regards

Alexander

• Proposed as answer by davidbaxterbrowneMicrosoft employee Wednesday, November 20, 2013 4:01 PM
• Unproposed as answer by SathyJay Thursday, November 21, 2013 10:58 AM

I'm asking without dropping the table and column also. 

Would it be possible?

Thanks!

I'm asking without dropping the table and column also. 

Would it be possible?

Thanks!

Without dropping table and column(without any change in structure), there is no possible way.

• Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Friday, November 29, 2013 3:08 AM

Thanks for your valuable reply!!!

I think one of way to alter column as identity property. Please try it out on test environment first.

 

1. Go to Tools>Designers>Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation."
2. Modify Account table -> Set Identity property to True

DECLARE @MaxId INT;

SET @MaxAId = (SELECT MAX(Id) + 1 FROM TABLENAME);

PRINT @MaxId

DBCC CHECKIDENT('TABLENAME', RESEED, @MaxId);

PRINT 'Complete'

You can always turn the function back on after you are happy with table structures change.