Auto Increment primary key in MS SQL server

Databases
1

How do you auto increment a primary key in SQL server?

I’ve got a form which passes some fields into an SQL server database. I would like the primary key to be created automatically when a new record is inserted.

I’ve done this in MySQL using auto_increment but can’t figure out how to do it in SQL Server(2000).

Any ideas?

2

Set the table field to be an identity column.

You can do this in Enterprise Manager in the Design Table mode or through SQL.

MS example:


CREATE TABLE jobs
(
   job_id  smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
   job_desc varchar(50) NOT NULL DEFAULT
   min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
)

The 1’s following the IDENTITY keyword indicate the SEED number (value for first record in table) and increment property (0 or 1).

3

AHA! That worked!

Thanks a lot shane, its easy once you know how but I would have floundered around for hours trying to figure that one out.

You the man.