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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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ç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Latest posts by Esat Erkeç (see all)