Auto Increment Primary Key in MS SQL Server

MySQL database has a great functionality and one of them is auto_increment. How to make a field as an auto_increment field in SQL Server 2000 ?

The key is, set the table field to be an identity column.

You can do this in Enterprise Manager in the Design Table mode or through SQL Query Analyzer. The code below is the SQL sample to create an auto_increment field :

CREATE TABLE trel_user_menu
(
usermenu_id int IDENTITY(1,1)PRIMARY KEY CLUSTERED,
menu_id smallint NOT NULL,
user_nick varchar(16) NOT NULL,
usermenu_status tinyint NOT NULL
)

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

Hope this helps.