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

SSIS Error : The value violated the integrity constraints for the column

 While Executing SSIS package getting error "The value violated the integrity constraints for the column" .

It means that it tried to insert null value into the non nullable column in the database. What you want to do is check the table definition of the destination table for any integrity constraints like NOT NULL or PRIMARY KEY.

You can distinguish from whether violation due to Not NULL or Primary Key by looking at Error column.

Generally if Error column is 0 (zero) then it is a PK violation otherwise it is a Not NULL key violation.

So need to analyze source data and correction if required.