Identity Column in SQL Server

What is Identity Column?

An identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an Auto Number field in SQL Server.

An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

If you mark a column as identity column need not require to supply value for that column.

Create a table using Identity Key:

CREATE TABLE tblPerson (
    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,
    [Name] varchar(50),
    [Gender] CHAR(1)
);
INSERT INTO tblPerson([Name], [Gender])
values
('John Doe','M'),
('Kimi Sharma','F')

Now you can see Person_id value is generated automatically.

Supply explicit values using Identity_Insert

Now delete one record from above 

delete from tblPerson where Person_id=1

(1 row affected)

Now lets try to insert new record.

INSERT INTO tblPerson([Name], [Gender])

values ('Rohit Kumar','M')

You can see new value assigned instead of assigning 1.

Now lets try to assign Person_id value manually

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Throws error -

Cannot insert explicit value for identity column in table 'tblPerson' when IDENTITY_INSERT is set to OFF.

Now set Identity_insert

set IDENTITY_INSERT tblPerson ON

Now try to insert same record:  

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Reset the Identity Column

If you have deleted all the rows in a table and you want to reset the identity column then use below command

delete from tblPerson

DBCC CHECKIDENT('tblPerson',RESEED,0)

INSERT INTO tblPerson([Name], [Gender])
values
('John Doe','M'),
('Kimi Sharma','F')

You can also see:

How to find last generated identity column value in SQL Server