Auto number

You can't alter a column and add identity property for that column.
Instead you can drop the existing column and create new column named ID with Identity property

alter table Emp Drop column ID;
ALTER TABLE [Emp] ADD [ID] int identity(1000, 1)

If you have data loaded into your Emp table, then create a new column named ID_new and then rename it to ID once ID values are autogenerated.

After that i try below code
ALTER TABLE [EMP] ALTER COLUMN [ID] INT NOT NULL;
ALTER TABLE [EMP] ADD PRIMARY KEY (ID);
Above code is worked properly

this below code is not working
SET IDENTITY_INSERT [Main] ON

i need to make ID cloumn as a Auto number .(i need to keep previous numbers) and when entering data need to create auto number from last previoud number for example last number is showing in Previous numbers 2301, then i need to start auto number 2302.thx

rrjegan17: i need to keep previous numbers. is it possible to create new auto number after that possible to insert previous numbers to new auto number fields.thx

If you want to start it from 2301 then this would do:

DBCC CHECKIDENT ("EMP", RESEED, 2301);

its showing Error called 'EMP' does not contain an identity column.

don't write this query

ALTER TABLE [Emp] ALTER COLUMN [ID] int identity(1000, 1))

Write this query

ALTER TABLE Emp
ALTER COLUMN ID
ADD CONSTRAINT IDENTITY(1000,1)

Have you created Identity column on EMP table using the statements given earlier.

showing error
samirbhogayta
Incorrect syntax near the keyword 'CONSTRAINT'.

lankapala,

Splitting points with my comments at least as Assisted solution should have been fair..