SQL Identity Insert

SQL Server provides us with an identity clause that allows you to create serial or auto-increment columns. If a column is set as an identity column, its values are generated automatically as new records are added to the table.

This provides you with the ability to have a unique identifier for each column without manual database inspection.

SQL Server Identity

In SQL Server, you can create an identity column as shown:

CREATE TABLE TABLE_NAME(
column_name INT IDENTITY
);

The specified column value above is automatically generated when you add a new record.

Consider the simple example shown below:

CREATE TABLE users(
id INT IDENTITY,
name VARCHAR(255)
);

In normal circumstances, we do not need to specify the value of an identity column during data insert. As mentioned, the value is auto-generated for every record insert as shown:

INSERT INTO users(name) VALUES ('Dorothy Michelle');

The above should insert the record into the table with a unique value for the id column. For example:

Notice the id column contains a value even though we didn’t specify one? That is the power of identity columns.

SQL Server Allow Identity Insert

But what if you attempt to add the value of an identity column manually? For example:

INSERT INTO users(id, name) VALUES (2, 'Jeff Arty');

The above query will return an error as shown:

SQL Server prevents you from explicitly adding values to an identity insert.

We can resolve this by enabling the identity insert feature in SQL Server.

The syntax for this command is as shown:

SET IDENTITY_INSERT TABLE_NAME ON

To enable identity insert on the users’ table, we can run:

SET IDENTITY_INSERT users ON;

To disable identity insert on a specific table, set the value to OFF.

SQL Server Reseed Identity Column

If you delete a record from the table, its identity’s column value is not re-used. This can lead to inconsistency and fragmentation in the database.

You can synchronize the identity column by reseeding as shown in the command below:

DBCC checkident (TABLE_NAME, RESEED, 1)

The above query should reseed the identity column of the specified table starting from index 1.

Conclusion

In this article, we explored the identity column in the SQL server and how to enable or disable it in a table.

I hope you found this helpful!

SQL Identity Insert

SQL Server Identity

SQL Server Allow Identity Insert

SQL Server Reseed Identity Column

Conclusion

About the author

John Otieno