SQL Standard

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:

    column_name INT IDENTITY

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

Consider the simple example shown below:

  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:


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


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.


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!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list