IDENTITY – autoincrement of column in SQL Server

In SQL Server autoincrement of column You may achieve by IDENTITY. Frequently it is column with primary key.

Through SQL Server Management Studio You may do it visual in this way. Course of creating table,  select column of int. In property of column expand  Identity Secification node.  For Is Identity from list choose Yes.

x01

Save table.

Thenceforth You may avoid set id into table. This palce in this column will be automatic  filling and increasing by 1.

insert into tab(nazwa) values ('element1');
insert into tab(nazwa) values ('element2');

And it is content of table:

x02

What is happend when there will be not any columns and after value clause we set null value.  What will to do SQL Server engine?

insert into tab values (null, 'element3');

We are given this result:

An explicit value for the identity column in table 'tab' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SQL Server don’t allow to do these action.

IDENTITY don’t restrict begin value by 1 and seed by 1? You may change it in property of column.

x03

In example above the start value is 100 and seed value is 5. So from 100 value will be increases by 5.

You may creating table through SQL query.

create table tab 
(
  id int PRIMARY KEY IDENTITY,
  nazwa varchar(50) not null
);

Use only IDENTITY mean to beginn from 1 and increase by 1.
In this way if You want to start from 100 and set seed to 5, You must after IDENTITY place in brackets these numbers. First it is start value and second is seed.

create table tab 
(
  id int PRIMARY KEY IDENTITY(100,5),
  nazwa varchar(50) not null
);

Changing  IDENTITY in SQL Server Management Studio ,example form (1,1) to (100,5) after save of table display warning window about problems.

x04

It will be another problem with IDENTITY clause. Imagine that it will be need to delete one of record from table. But later the record You must put in table back. How to do it when this column is autoincrement by IDENTITY.

insert into tab values( 12,'element3');

After doing this query above You get this information:

An explicit value for the identity column in table 'tab' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Solution this problem is SET IDENTITY_INSERT.
After SET IDENTITY_INSERT You must place name of database, scheme and name of table  and ON, if You want to turn on option, or OFF if You this option turn off. Besides these in insert query You must provide the names of columns. So first we set for tab table IDENTITY_INSERT option by ON.

SET IDENTITY_INSERT BazaTest.dbo.tab ON;

Next You may modify insert query:

insert into BazaTest.dbo.tab(id, nazwa) values( 12,'element3');

Turn on this option to allow  insert any value  in id column.
In this moment You may execute query.

x05

After it the IDENTITY_INSERT must be turn off.

SET IDENTITY_INSERT BazaTest.dbo.tab OFF;

Remember that if the IDENTITY_INSERT option is turn on, the same value  stored in table may not be insert. The primary key don’t allow to do it.

Violation of PRIMARY KEY constraint 'PK__tab__3213E83F13A189C3'. Cannot insert duplicate key in object 'dbo.tab'. The duplicate key value is (12).