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.
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:-
So far in this article we learnt How to Add an Identity on Column in SQL SERVER.