An identity column in table generates key values automatically based on the current seed and increment, and the benefit of creating an identity column is that you do not need to provide the value for an identity column which is automatically generated by SQL Server during the insertions.
Below is the syntax:
IDENTITY [ (seed , increment) ]
Seed: Is the value that is used for the very first row loaded into the table.
Increment: Is the incremental value that is added to the identity value of the previous row that was loaded.
You must specify both the seed and increment or neither. If not specified, the default is (1,1).
CREATE TABLE dbo.CustomerDetails ( ID INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(50), City VARCHAR(20) )
Now, we have created a table having an identity column named ID, when any records is inserted in this table, an identity column value will be 1 for first row then next row will have incremental value that is added to the identity value of the previous row that was loaded.
Let’s insert two records in table using below scripts.
INSERT INTO dbo.CustomerDetails (Name, City) VALUES
('Raj Malhotra', 'Delhi'),
('Manoj Singh', 'Mumbai')
Now you can see, we have not provided the value for identity column during the insertion while the values for identity column gets generated by SQL Server automatically.
SELECT * FROM dbo.CustomerDetails
Now, delete one record from table for Id =2.
DELETE FROM dbo.CustomerDetails WHERE Id =2
Now, you can see record has been deleted successfully, and we have only one record left in table.
SELECT * FROM dbo.CustomerDetails
Now, What happen when a new record is inserted in table. As you can see the last identity value in table is 1. Ideally, when a new record is inserted in table the identity value for that column should be 2.
But this is not the case, if you check the last identity value for table using below scripts.
You get to know that the last identity value for the table is 2 not 1, means if you insert new records the identity value forย that records will be 3.
DBCC CHECKIDENT ('dbo.CustomerDetails')
Let’s insert a record in table using below scripts.
INSERT INTO dbo.CustomerDetails (Name, City)
VALUES ('Mukesh Raj', 'Gurgaon')
Now, you can see the identity value for newly added record is 3. Ideally, it should be 2 because the last identity value before inserting a record was 1.
It happens, when any errors occurs while inserting record to the table or record is deleted from the table the identity value not resets.
Now, lets see how to reset identity value.
First, delete the record for Id =3ย using below scripts.
DELETE FROM dbo.CustomerDetails WHERE ID = 3
Now you can see record has been deleted.
SELECT * FROM dbo.CustomerDetails
Lets, check the identity column value again, you can see it is 3.
DBCC CHECKIDENT (‘dbo.CustomerDetails’)
Lets reset the identity value to 1, so that when a new records is inserted in table that will have identity value 2.
DBCC CHECKIDENT ('CustomerDetails', RESEED, 1)
You can see above message, now identity value is reset to 1.
Now, insert a same record in table.
INSERT INTO dbo.CustomerDetails (Name, City)
VALUES ('Mukesh Raj', 'Gurgaon')
You can see, the identity value for this record is 2.
Note that, when you run truncate command on table an Identity column in a table is reset to seed value automatically.
Also Read..
