USE TEST GO
CREATE TABLE dbo.Person( ID INT IDENTITY(1,1), Name VARCHAR(10))
INSERT INTO dbo.Person(name)
INSERT INTO dbo.Person(Name)
VALUES ('Aamir Shahzad')
--Check the Identity Value of a Column
DBCC CHECKIDENT ('dbo.Person');
Fig 2- DBCC CHECKIDENT to see the Identity Value
Even the insert was failed for second record but the identity value is incremented. If we will insert next record, the identity value will be 3 for that as we can see in Fig 3.
Here are couple of ways to reset the identity value of a column after deleting data.
Reset Identity Value By Using DBCC CHECKIDENT:
DBCC CHECKIDENT ('dbo.Person', RESEED, 0);