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:
[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
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:
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)
values
('John Doe','M'),
('Kimi Sharma','F')
You can also see:
How to find last generated identity column value in SQL Server