When columns have the identity property set on them, the value automatically increases on every row, depending on the seed and incrementing value.

So let us create a table with an identity column

CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeName] [char](10) NULL
) ON [PRIMARY]

And now let us add some data in that table:

If you say

SELECT * FROM TableA ; you will see that the ID column contains value from 1 to 4.

Now let us say that the user goes ahead and deletes ID 2

DELETE FROM TableA where ID = 2 ;

If you now do a SELECT * FROM TableA

There is a gap in between ID 1 and 3. If you try to explicitly add a value to the identity column to fill up the gap using the statement:

INSERT TableA(ID,SomeName) VALUES(2,'R')

You will get an error “Cannot insert explicit value for identity column in table 'TableA' when IDENTITY_INSERT is set to OFF”

In order to insert a value into an Identity column, set Identity to ON. Use the code below to do so:

SET IDENTITY_INSERT TableA ON

INSERT TableA(ID,SomeName) VALUES(2,'R')

SET IDENTITY_INSERT TableA OFF

Setting Identity_Insert to ON removes this check. Once you have inserted the record, set it back to OFF again.