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.
