Identity column in SQL

While creating a table if you give identity to any column, then you do not need to pass values to that column they will be generated automatically. For identity we need to provide seed and increment of course they are optional if you do not specify the it will take by default 1.


The following is the table.

create tabel blogger(bloggerid int identity(1,1),name varchar(100))

Now insert the values to the blogger table.

insert into blogger values('sai')
insert into blogger values('sandeep')

Now select the values from the table then you will see that the name sai will have bloggerid 1 and sandeep will have bloggerid 2.

If you are trying to insert the values like below you will get an error. This means that user can not insert values explicitly.

insert into blogger values(1,'sai')

Now if you delete the bloggerid 2 then it will be deleted, but while inserting another value then bloggerid will be 3. If you want to insert explicitly then you need to turn on the identity insert as follows.

SET Identity_Insert tablename ON

In our example, we have created a table blogger, so we need to write the query as follows  by adding column names also.

SET Identity_Insert blogger ON

Now insert the values into the blogger table as follows.

insert into blogger(bloggerid, name) values(2,'dummu')

So here we have inserted values explicitly, now if you want to turn off the identity_insert write the following query.


SET Identity_Insert blogger OFF


Even though if you deleted the values from the table, if you again insert the values, it's count starts from last blogger id. If you want to reset the seed, then use the following command.

DBCC CHECKIDENT(blogger, RESEED, 0)