How to Add an Identity on Column in SQL SERVER

In this article we will learn about How to Add an Identity on Column in SQL SERVER. 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. Please read my previous article Different Types of SQL Keys: Example and Uses.

What is Identity in SQL Server?

Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table.

Syntax

IDENTITY [( seed, increment)]


Seed: Starting value of a column. 
      Default value is 1.

Increment: Incremental value that is 
added to the identity value of the previous 
row that was loaded. The default value 1.

Create the table and add an identity  on the column.

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

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)

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.

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:-

Conclusion

So far in this article we learnt How to Add an Identity on Column in SQL SERVER.