hi Experts,

when i alter non identity column to identity column
using this Query
alter table testid
alter column test int identity(1,1)

then i got this error message

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'identity'.


thanks

You cannot alter any column to set identity value.

means you cannot create identity property by altering column.


only solution is to create a new column settting identity value true and then remove previous column.

like


alter table tablename
add columname int identity(1,1) not null

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.