Introduction to SQL Server Identity

Total: 4 Average: 4.5

In this article, we will explore the basics and details of the SQL Server IDENTITY property and the IDENTITY column features. Also, we will consider how to insert explicit values to the identity columns through the IDENTITY_INSERT feature.

Introduction to SQL Server IDENTITY property and IDENTITY column

In SQL Server, the identity property allows us to create identity columns in the SQL Server tables according to settings of the identity property syntax. The syntax of the identity property looks as follows, and we apply this syntax to create or alter a table statement.

IDENTITY [(seed, increment)]

At first, we will examine the parameters of the identity property. This property takes two input parameters: the first one is seed and the second one is increment. The seed parameter specifies that the first starting value of the inserted value into the table and the Increment parameter defines the increment value of the inserted data.

Now, we will intensify this principal definition of identity property with some examples.

How to create Identity column in SQL Server

Example-1: In the following example, we will create an identity column and the first value will start from 1 and increment 1 by 1.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

GO

INSERT INTO TestIdentity
VALUES ('The first inserted row')

INSERT INTO TestIdentity
VALUES ('The second inserted row')

INSERT INTO TestIdentity
VALUES ('The third inserted row')

INSERT INTO TestIdentity
VALUES ('The fourth inserted row')

SELECT Id as [IdentityColumn],Col1 FROM TestIdentity

Example-2: In the following example, we will create an identity column and the first value will start from 37 and increment 20 by 20.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(37,20) ,
Col1 VARCHAR(100))

GO

INSERT INTO TestIdentity
VALUES ('The first inserted row')

INSERT INTO TestIdentity
VALUES ('The second inserted row')

INSERT INTO TestIdentity
VALUES ('The third inserted row')

INSERT INTO TestIdentity
VALUES ('The fourth inserted row')

SELECT Id as [IdentityColumn],Col1 FROM TestIdentity

As you can see in the above example, the identity property provides generation of the auto-increment values according to seed and identity parameters.

How to insert explicit values to SQL Server identity column?

By default, identity property does not allow us to insert explicit values into the identity columns. If you try to insert explicit values into an identity column, you will experience the following error.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

GO

INSERT INTO TestIdentity
VALUES (1,'The first inserted row')

Msg 8101, Level 16, State 1, Line 9
An explicit value for the identity column in table, ‘TestIdentity’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

We can overcome this error by enabling the IDENTITY_INSERT feature of the table. Now, we will change the insert statement as follows.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

SET IDENTITY_INSERT TestIdentity ON

INSERT INTO TestIdentity (Id,Col1)  
VALUES (1,'The first inserted row')

SET IDENTITY_INSERT TestIdentity OFF

SELECT * FROM TestIdentity

Another key point about this issue is that we have to write a column list to the insert the statement. If we don’t do this, we will experience the following error.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

SET IDENTITY_INSERT TestIdentity ON

INSERT INTO TestIdentity   
VALUES (1,'The first inserted row')

SET IDENTITY_INSERT TestIdentity OFF

Msg 8101, Level 16, State 1, Line 9
An explicit value for the identity column in table, ‘TestIdentity’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

This error defines that column list is missing in the insert statement. In the below insert statement, we will fix this error.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

SET IDENTITY_INSERT TestIdentity ON

INSERT INTO TestIdentity  (Id,Col1) ---column list
VALUES (90,'The first inserted row')

SET IDENTITY_INSERT TestIdentity OFF

In some cases, we need to insert data to one table from another table. One of the best ways to perform this operation is to use the “INSERT INTO SELECT” statements. However, if the target table has an identity column, we need to enable the IDENTITY_INSERT option in the target table. Also, we have to write the column list of the target table.

DROP TABLE IF EXISTS TestIdentity
DROP TABLE IF EXISTS SourceTable 
CREATE TABLE SourceTable (ID INT,
Val1 VARCHAR(100))
INSERT INTO SourceTable 
VALUES (1,'First Row'),(2,'Second Row')
GO
CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))
SET IDENTITY_INSERT TestIdentity ON
INSERT INTO TestIdentity  (Id,Col1) 
SELECT * FROM SourceTable
SET IDENTITY_INSERT TestIdentity OFF

The drawback of the IDENTITY_INSERT option is that it can cause a gap between the identity column values. A statement similar to the following one will generate a gap between identity column values.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

INSERT INTO TestIdentity  ---column list
VALUES ('The first inserted row (INDENTITY_INSERT_OFF)')

SET IDENTITY_INSERT TestIdentity ON

INSERT INTO TestIdentity  (Id,Col1) ---column list
VALUES (90,'The second inserted row (INDENTITY_INSERT_ON')

SET IDENTITY_INSERT TestIdentity OFF


INSERT INTO TestIdentity   ---column list
VALUES ('The third inserted row (INDENTITY_INSERT_OFF)')


SELECT * FROM TestIdentity

DBCC CHECKIDENT command

The DBCC CHECKIDENT command allows us to get details about the identity column last value. This function also allows resetting and changing the identity column current value to another value. Now, we will get the last value of the identity through the DBCC CHECKIDENT command.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))

GO

INSERT INTO TestIdentity
VALUES ('The first inserted row')

INSERT INTO TestIdentity
VALUES ('The second inserted row')

INSERT INTO TestIdentity
VALUES ('The third inserted row')

INSERT INTO TestIdentity
VALUES ('The fourth inserted row')

DBCC CHECKIDENT ('TestIdentity', NORESEED)

Another option about the DBCC CHECKIDENT command is resetting the identity column to a required value. In the following example, the RESEED parameter changes the max value of the identity column to 100 and the subsequently inserted values use this max value.

DROP TABLE IF EXISTS TestIdentity
CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))
GO
INSERT INTO TestIdentity
VALUES ('The first inserted row')
INSERT INTO TestIdentity
VALUES ('The second inserted row')
INSERT INTO TestIdentity
VALUES ('The third inserted row')
INSERT INTO TestIdentity
VALUES ('The fourth inserted row')
GO
DBCC CHECKIDENT ('TestIdentity',RESEED,100)
GO
INSERT INTO TestIdentity
VALUES ('The fifth inserted row')

SELECT  * FROM TestIdentity

SQL Server identity column and uniqueness

Identity columns do not guarantee the generation of unique values. This is the common confusing issue about the identity columns, so if we want to ensure the uniqueness of the generated values, we can use the unique index for these columns. Now, we will prove and demonstrate how to create duplicate values over the identity columns.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) ,
Col1 VARCHAR(100))
SET IDENTITY_INSERT TestIdentity ON
INSERT INTO TestIdentity  (Id,Col1)
VALUES (1,'The first inserted row (INDENTITY_INSERT_OFF)')

INSERT INTO TestIdentity  (Id,Col1)
VALUES (1,'The first inserted row (INDENTITY_INSERT_OFF)')


SET IDENTITY_INSERT TestIdentity OFF


SELECT  * FROM TestIdentity

In addition, Identity Columns and Primary Keys are two dissimilar objects in SQL Server. The usage purpose of the identity column is to generate the auto-incremented number. On the other hand, the primary key constraint guarantees and provides the uniqueness of the values in a particular column. The Primary Key constraint enforces the unique values for the defined columns because, by default, Primary Key creates a clustered unique index in the table. In the common usage, the Primary Key constraint and Identity Property can be utilized together. This usage approach helps us to bring the flexibility of the Primary Key uniqueness and the Identity auto-increment feature to the applied column. In the following example, we will also add a primary key constraint to Id column and it will prevent to duplicate inserted values.

DROP TABLE IF EXISTS TestIdentity

CREATE TABLE TestIdentity
(Id INT IDENTITY(1,1) PRIMARY KEY,
Col1 VARCHAR(100))
SET IDENTITY_INSERT TestIdentity ON
INSERT INTO TestIdentity  (Id,Col1)
VALUES (1,'The first inserted row (INDENTITY_INSERT_OFF)')

INSERT INTO TestIdentity  (Id,Col1)
VALUES (1,'The first inserted row (INDENTITY_INSERT_OFF)')
SET IDENTITY_INSERT TestIdentity OFF


SELECT  * FROM TestIdentity

When we navigate the Indexes tab of the TestIdentity table in the object explorer, we can find out a unique clustered index which created by primary key constraint. This constraint enforces to unique values for Id column.

Conclusion

In this article, we discussed the fundamental concepts and usage methods of SQL Server Identity property and Identity columns.

References

Esat Erkeç

Latest posts by Esat Erkeç (see all)