Reset Identity Column

Even though we are deleting all the records in a table (which includes Identity column) sql server maintains Identity value.To resolve this problem we can use RESEED option.

DBCC CHECKIDENT (tablename, RESEED, 0)

Note : we can also use truncate table when there is no foreign key relationship

Example

--******** Reset Identity Column Value **********

CREATE TABLE test(id INT IDENTITY, NAME VARCHAR(10))

INSERT INTO test(name ) VALUES('a')
INSERT INTO test(name ) VALUES('a')
INSERT INTO test(name ) VALUES('a')

DELETE FROM test

-- The above statement will delete the record but maintain the identity value.

INSERT INTO test(name) VALUES ('a')

SELECT * FROM test

Output
id name
4 a