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,
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.
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!