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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/04/echo/sql-identity-insert-01.png" data-lazy- height="60" src="data:image/svg xml,” width=”624″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/04/echo/sql-identity-insert-02.png" data-lazy- height="44" src="data:image/svg xml,” width=”624″>
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!
About the author
<img data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2022/04/echo/john-150×150.png6265d47808207.jpg" height="112" src="data:image/svg xml,” width=”112″>
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
