Auto generated incremental identity number field in MSSQL table

When we add a record to table, MSSQL db can assign unique incremental identity to the record added by use of identity property of a column. This property is used in many scripts where a auto generated number is required to identify a particular entry. Here are some requirements of such a property.

When a member or customer signups at website, a member id or customer id can be generated automatically and displayed to the new member. This id can be quoted by the member for further details.

For an online complaint or query registration process one unique identification number is generated after recording the query. This identification number is used for further processing and tracking of the query.

Let us create one table where one field name id is used at identity , here is the sql script to create a table.


CREATE TABLE [dbo].[auto] (
	[id] [int] IDENTITY (100, 2) NOT NULL ,

	[name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[city] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

) ON [PRIMARY]

The above table auto can be created by using enterprise manager also. Note that the column id is the identity column which will start the first record with value 100 ( Identity seed ) and next record with the value 102 ( as we kept 2 as the step or known as Identity Increment )

We need not specify the value of the id field as that will be generated automatically. We will only insert the values for name and city in the record.

Here is the SQL for adding a record to this table


insert into auto (name,city) values('John Reid', 'London')

With this query one record will be added and MSSQL will automatically assign next available incremental number as the ID field value.

Using Microsoft SQL server management studio

We need to first set the field as int or decimal then in column properties list below we need to select Identity Specification , is identity to be set to Yes. We will get this options only after selecting the Data Type to int.

We can also select Identity Increment and Identity Seed values.

Read how to create auto increment columns in MySQL table

Next we will read how to collect the just added auto increment number and display to the new member.

Creating column for incremental auto generated record number in MSSQL table

Using Microsoft SQL server management studio