Autoincrement of primary key?

XiaoMei
XiaoMei used Ask the Experts™
on
Hi all,

How do I make a column in MS SQL to autoincrement everytime I insert a row?

From the example below, I would like the id to increment everytime I insert a new row.

create table tmp (id int, name varchar(20))

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
This shd do it -

create table tmp (id int IDENTITY (1,1) NOT NULL, name varchar(20))
If you want the id field to be the primary key as well:

CREATE TABLE tmp
(
id INTEGER IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20)
)

Making the id a primary key automatically ensures it won't be NULL.

Cheers
In enterprise manager, chose to design the table you want to change, in the columns window at the bottom, c

hange identity to yes.
Identity seed is the number to start with(after a truncate table or creating the table from scratch.)
Identity increment is the number to increment on every insert. (eg 1, 2, 3, 4 or 10, 20, 30...)

ADW
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
remember, you can only set a column as IDENTITY when you first create the table.. once it's been created, a column can't be set as IDENTITY..
-P
Yes it can...  Try it., as long as the field was an integer, and it's already incramented.

Author

Commented:
thanks for the help =)
Force-Accepted.

SpideyMod
Community Support Moderator @Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial