If we want to have a column where value should inserted automatically in incremented order, we can apply  Identity on that column.By default we can not insert value manually in Identity column.


SYNTAX:-

Identity (<seed> < Incremented >)
There are some steps to know about identity column. which are given below:-

Step1:- Create the table and add an identity  on the column.


CREATE TABLE Employee
 ( EID INT IDENTITY(0816113042,1),
   ENAME VARCHAR(30), EAGE INT
 )

Step2:-  Add an identity on column in existing table.
  • we can add identity column on the table,if an identity column already exist then we use following command.
ALTER TABLE Employee DROP COLUMN EID

ALTER TABLE Employee ADD EID INT IDENTITY(10,1)
  • we can apply identity constraint on the column when table is already created.
ALTER TABLE Employee ADD ToBeIdentity INT IDENTITY(1,1)

Step3:-   Insert values manually on identity column.


SET IDENTITY_INSERT Employee ON
            --OR
 SET IDENTITY_INSERT Employee OFF

INSERT INTO Employee (ENAME,EAGE)VALUES('Jayant',33)

INSERT INTO Employee (ENAME,EAGE)VALUES('Sri',31)

The Final output should like this.



Step4:- We can also apply identity constraint on column without SQL Commands.
first click eid column -> click Identity Specification-> now set is identity to YES .Which is shown  below:-