Have you ever come a cross where you identity column seeds breaks because there are some data deleted from the table. If so you are not alone. Many time data can be removed from the table. Now the new insert is picking up from the last value of the seed.
CREATE TABLE dbo.Itest(
id int IDENTITY(1,1) NOT NULL,
name varchar(10) NULL,
gender varchar(2) NULL,
numberofF int NULL,
PRIMARY KEY CLUSTERED
(
id ASC
)
)
Insert some data in the table
INSERT INTO Itest VALUES ('Pavel','F',3)
GO 5
SELECT * FROM Itest
1 Pavel F 3
2 Pavel F 3
3 Pavel F 3
4 Pavel F 3
5 Pavel F 3
Delete data from the table
DELETE Itest WHERE ID = 5
Insert some data again in the table.
INSERT INTO Itest VALUES ('Nitol','M',4)
GO 4
SELECT * FROM Itest
1 Pavel F 3
2 Pavel F 3
3 Pavel F 3
4 Pavel F 3
6 Nitol M 4 --------- See the identity breaks here.
7 Nitol M 4
8 Nitol M 4
9 Nitol M 4
What can you do about it? We need to reseed the table
Let us remove the data where identity has been broken.
DELETE Itest WHERE id > 4
Reset the seed value
DBCC CHECKIDENT ('itest', reseed, 4)
Insert the data again
INSERT INTO Itest VALUES ('Nitol','M',4)
GO 4
SELECT * FROM Itest
1 Pavel F 3
2 Pavel F 3
3 Pavel F 3
4 Pavel F 3
5 Nitol M 4 -- More more Issue
6 Nitol M 4
7 Nitol M 4
8 Nitol M 4
