Auto increment primary key in SQL Server Managemen

Question

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how.

I've looked the the properties but can't see an option, I have seen an answer where you go to the Identity specification property and set it to yes and set the Identity increment to 1, but that section is grayed out and I can't change the no to yes.

There must be a simple way to do this but I can't find it.

Answer 1

If the table is already populated it is not possible to change a column to IDENTITY column or convert it to non IDENTITY column. You would need to export all the data out then you can change column type to IDENTITY or vice versa and then import data back. I know it is painful process but I believe there is no alternative except for using sequence as mentioned in this post.

Answer 2

When you're creating the table, you can create an IDENTITY column as follows:

CREATE TABLE (
  ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ...
);

The IDENTITY property will auto-increment the column up from number 1. (Note that the data type of the column has to be an integer.) If you want to add this to an existing column, use an ALTER TABLE command.

Edit:
Tested a bit, and I can't find a way to change the Identity properties via the Column Properties window for various tables. I guess if you want to make a column an identity column, you HAVE to use an ALTER TABLE command.

Answer 3

Make sure that the Key column's datatype is int and then setting identity manually, as image shows

Or just run this code

-- ID is the name of the  [to be] identity column
ALTER TABLE [yourTable] DROP COLUMN ID 
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)

the code will run, if ID is not the only column in the table

image reference fifo's