Setting auto increment column / Identity column on the column in SQL SERVER

Author : Prakash Pradeep Gopu

To Insert the unique values in the table we will use the Auto increament column on the table.It will generate automatically unique number for newly insert in table .

Method 1 :

Open SQL server  2008 and connect to local server  >> Click on Data Bases >> Click on the Database you want to create a table >> click on Tables >> Right click select New Table (It will open the New Table design mode as shown in the figure) >> Create a Table With your desired column with first column as Identity column.The Identity column data type must be int.

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 IsIdentity 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 the above figure we have another property called Identity Seed this property is used to set starting value of column. Suppose if set the Identity Seed value is 100 then the first insertion will take the value 100 and next insertion will take value 101 and so on…

Now we are set the column with the identity column .Now save your table  and insert two rows in your table to test.

Ex : Insert into dbo.Login (UserName,Pwd) VALUES ('Prakash', 'Pradeep')

Method 2 :

Identity Property Creates an identity column in a table.

 Syntax :IDENTITY [ ( seed , increment ) ]

seed

Is the value that is used for the very first row loaded into the table.

increment

Is the incremental value that is added to the identity value of the previous row that was loaded.

Copy and paste the follwing code on sql server query analyser .

CREATE TABLE UserDetails

(

UserId int PRIMARY KEY IDENTITY(1,1),

UserName varchar(50),

Pwd varchar(50),

)