Auto Increment primary key in MS SQL server

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?

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:

   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).

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.