Auto-increment primary key in SQL tables

Using Sql Express Management Studio 2008 GUI (not with coding), how can I make a primary key auto-incremented?

Let me explain: there is a table which has a column named "id" and the items of this column are set to be primary keys. I want to make this column auto-incremented, but ho开发者_开发知识库w?

Cheers

1. Presumably you are in the design of the table. If not: right click the table name - "Design".
2. Click the required column.
3. In "Column properties" (at the bottom), scroll to the "Identity Specification" section, expand it, then toggle "(Is Identity)" to "Yes".

Although the following is not way to do it in GUI but you can get autoincrementing simply using the IDENTITY datatype(start, increment):

CREATE TABLE "dbo"."TableName"
(
   id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   name varchar(20),
);

the insert statement should list all columns except the id column (it will be filled with autoincremented value):

INSERT INTO "dbo"."TableName" (name) VALUES ('alpha');
INSERT INTO "dbo"."TableName" (name) VALUES ('beta');

and the result of

SELECT id, name FROM "dbo"."TableName";

will be

id    name
--------------------------
1     alpha
2     beta

Right-click on the table in SSMS, 'Design' it, and click on the id column. In the properties, set the identity to be seeded @ e.g. 1 and to have increment of 1 - save and you're done.

for those who are having the issue of it still not letting you save once it is changed according to answer below, do the following:

tools -> options -> designers -> Table and Database Designers -> uncheck "prevent saving changes that require table re-creation" box -> OK

and try to save as it should work now

I don't have Express Management Studio on this machine, so I'm going based on memory. I think you need to set the column as "IDENTITY", and there should be a [+] under properties where you can expand, and set auto-increment to true.

I think there is a way to do it at definition stage like this

create table employee( id int identity, name varchar(50), primary key(id) ).. I am trying to see if there is a way to alter an existing table and make the column as Identity which does not look possible theoretically (as the existing values might need modification)