Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Create Identity Column or Auto Increment Column

Feb 12, 2012
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 RSS subscribe by email Subscribe by Email

20 comments :

Anonymous said...

Amazing....
I ever seen this kind of website.

Anonymous said...

Sir

I WANT TO INCREMENT CUSTOMER OR EMPLOYEE ID TO INCREMENT LIKE THIS

08U13001
08U13002

CAN I USE IDENTITY PROPERTY OF SQL

THANK YOU

Suresh Dasari said...

To set 08U13001,08U13002 type id's it's not possible with identity column because we can set identity column with integer datatype only. integer datatype column won't allow us to use characters.

Anonymous said...

super website sir... what a talent...

suresh gupta said...

How to access distributed database in asp.net?

Dinesh said...

sir i m agetting error like this while entering data from asp.net page. "an explicit value for the identity column in table "xyz" can only be specified when a column list is used and IDENTITY_INSERT is ON" give me suggestion as soon as possible..plz

Suresh Dasari said...

@ Comment 2...
To set 08U13001,08U13002 type id's check this post here i explained clearly http://www.aspdotnet-suresh.com/2012/04/set-custom-auto-generatedincrement.html

sohaib said...

but in the similar way if i want one record forexample.. from user ids...1 ,2,3,4,5,6... i delete userid 4... how can i reorder the ids to become the same as 1,2,3,4,5... user id 5 will become 4 and user id 6 will become 5... wats the procedure to adjust the primary ids automatically with the deletion of record...

yeswanth said...

sir iam a great fan of you and your website.. sir i need sql server table for UserDetails which you used in filtering gridview parameters.. it would be helpful if u post this for me

Pallavi said...

How can i auto increment a column(like sl no.) in reports.
Please help.
Thank you

Suresh Dasari said...

@yeswanth
check this post http://www.aspdotnet-suresh.com/2011/12/search-records-in-gridview-and.html

Suresh Dasari said...

@Pallavi....
if you set the this identity column automatically generate serial numbers if we insert record. If you want custom auto incremented column check this post
http://www.aspdotnet-suresh.com/2012/04/set-custom-auto-generatedincrement.html

Unknown said...

This is helpful for me

thank you................

satya said...

Hi suresh,
I have one requirement that , user session should be logged off when user clicks on browser or browser tab. Please provide me any inputs/ solution approach for this problem as soon as possible.
Thanks in advance.

kalyan said...

sir i want increment the emp id automatic generator in web page

Vinod Kamble said...

H.
I want a query which display data like below

Id EmpNo Name
1 Emp-2013-2014-0001 ABC
2 Emp-2013-2014-0002 XYZ
.................................................after 2013
10 Emp-2014-2015-0001.........PQR .... .again starts from one.
11 Emp-2014-2015-0002.........DEF....

plz help me thx in advance email me on vinodkamble28489@gmail.com...

Hope u will do well...!@! :)

Anonymous said...

How to skip this column while inserting into table, since we are using auto increment. plz provide solution for asp.net.

Unknown said...

sir, i don't understad how can i do

Unknown said...

i create table in sql but when i insert into table values error comes how can i do
and i have no c# code

Unknown said...

please sir help me

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.