SQL Server - Create Identity Column or Auto Increment Column

Introduction:

Here I will explain how to set or create auto increment column or identity property on column in SQL server.

Description:

In many situations we will insert records in table during that time we need to set unique value for that record if we use auto increment column then automatically generate unique number for newly insert record in table (Like generate EmployeeID of each employee whenever employee record inserted).

To set auto increment column in table we have different methods

First Method

After completion of table creation open table in design mode and select column for which we need to set identity column. Now go to column properties in that select Identity Specification >> Is Identity

Now change the property of Is Identity from “NO” to “YES”

After change Is Identity property to “YES” Give Identity Increment value (This is the value which will add for every row inserted) generally this value will be 1 whenever new record inserted column value increases 1 if you want to more value change that value.

If you observe we have another property called Identity Seed this property is used to set starting value of column. Suppose if you want to start column value from 1000 change Identity Seed value from 1 to 1000.

Now our column is ready here we need to remember one point that is to insert new record in table we don’t want to specify value for the column which contains Identity property automatically unique value will added for that column.

Ex:  Insert into UserDetails (UserName, FirstName, LastName, Email) VALUES (sureshdasari, Suresh, Dasari, suresh@gmail.com)

After insertion our table will be like this

UserId	UserName	FirstName	LastName	Email
1	sureshdasari	Suresh	Dasari	suresh@gmail.com

Second Method

We can set auto increment column through query like this

CREATE TABLE UserDetails ( UserId int PRIMARY KEY IDENTITY, UserName varchar(200), FirstName varchar(255), LastName varchar(255), Email varchar(255) )

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email