Set IDENTITY to existing column

Question

I have a table which is having a column name ID field of integer type. It was declared IDENTITY. And it has data according to IDENTITY. But recently I removed IDENTITY column from that table. Now I want to change that to IDENTITY again. But this query says incorrect syntax

Alter table FuleConsumptions alter column  TransactionID INT IDENTITY(1,1);

But I can perform the same task using SQL server designer in properties of the table. What am I doing wrong here?

Answer 1

I think The identity column will hold the sequence of number thats why it thrown error better you drop column then create it again and set IDENTITY

Alter Table FuleConsumptions Drop Column TransactionID
Go
ALTER TABLE FuleConsumptions
ADD TransactionID int; 
go
Alter table FuleConsumptions alter column  TransactionID INT IDENTITY(1,1);