Identity Column in SQL Server

Harpreet Singh
Jan 31, 2015

33.3k
0
0
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

This article explains how to supply explicit values for an identity column and how to reset it.

Identity Column

Identity columns are columns with values automatically generated when a new row is inserted into a table.

Let's look at an example.

Example without identity column

I have created a database named “IdentityDatabase” and in that I have created a first table named “NonIdentityTable” that has the two columns Id (primary key) and Name.

CREATE DATABASE IdentityDatabase;
GO
USE IdentityDatabase;
GO
Create table NonIdentityTable(
Id INT CONSTRAINT pk_Id PRIMARY KEY,
Name NVARCHAR(20) NOT NULL
);
GO
INSERT INTO NonIdentityTable (Id,Name) VALUES (1,'abc'),(2,'def'),(3,'ghi'),(4,'jkl');

Above, I have inserted four rows by specifying the values for both the columns.

(If the column is a non-identity column, then we need to pass the value for that column.)

If you executed the following query, you will get the output as:

SELECT * FROM NonIdentityTable;

Now what if I don't want to insert the values for the Id column and whenever I insert a new row, I want the values for the id column to be inserted automatically? For that we can create an Identity column.

Example using identity column

In the same database I have created another table named “IdentityTable” that has two columns, Id and Name, where the Id column is an identity column and also a primary key column.

Create table IdentityTable(
Id INT IDENTITY(1,1) CONSTRAINT pk__Id PRIMARY KEY,
Name NVARCHAR(20) NOT NULL
);

IDENTITY(1,1)

The first parameter is the seed (from where the value will start) and the second parameter is the increment value.

Note: Seed and Increment values are optional. If you don't specify it, then by default they both are 1.

Let's try to insert rows by providing values for both of the columns as in the following:

INSERT INTO IdentityTable(Id,Name) VALUES (1,'abc'),(2,'def'),(3,'ghi'),(4,'jkl');

We get an error. So, when a column in a table is an identity column then we won't be able to insert the values for that column explicitly.

INSERT INTO IdentityTable(Name) VALUES ('abc'),('def'),('ghi'),('jkl');

SELECT * FROM IdentityTable;

Let's look at another example, where you must delete a record with an id value of 2.

DELETE FROM IdentityTable WHERE Id = 1;

SELECT * FROM IdentityTable;

But by mistake you have deleted the wrong record and you want to retrieve that record.

In this kind of situation what can do is to write an insert query as in the following:

INSERT INTO IdentityTable (Id,Name) values (1,'abc');

But when you execute the preceding query, you will get an error stating:

So, first we need to turn the Identity_Insert for IdentityTable to ON.

Syntax

SET IDENTITY_INSERT tablename ON;

Query

SET IDENTITY_INSERT IdentityTable ON;

The preceding query will allow us to insert the value for the Id column explicitly.

INSERT INTO IdentityTable (Id,Name) values (1,'abc');

SELECT * FROM IdentityTable;

Note: Always set the Identity_Insert for a table to ON, only when these kind of issues occur.

To turn off

SET IDENTITY_INSERT IdentityTable OFF;

Let's look at another example, where there is requirement to delete all the records and add new records.

To delete all the records from a table, we write the following query:

Syntax

DELETE FROM tableName;

Query

DELETE FROM IdentityTable;

Currently all the records are deleted. So, now let's add some new records.

INSERT INTO IdentityTable(Name) VALUES ('Sam'),('Aiden'),('James'),('Sara');

SELECT * FROM IdentityTable;

We have added the new records to our table. But there is a slight problem in the Id column. After deleting the old records, the Id column still remembers the last incremented value, in other words 4, and when we insert a new record it increments by 1 and inserts the value 5 and so on. But we want the Id value to start from 1 and for that it is very important to reseed the Identity columns, whenever you delete all the rows.

But first remove the recently added rows from the table again.