Reseed Identity is behaving differently in New Table and in Existing Table

Reseed Identity is behaving differently in New Table and in Existing Table

If you execute the DBCC CHECKIDENT ('Tablename',reseed,0) in an existing database and a newly created database,the command is behaving totally different way.

Example below

CREATE TABLE Test1
(
  Id int identity(1,1) primary key,
  name nvarchar(10) )

DELETE FROM Test1

EXEC DBCC CHECKIDENT ('Test1',reseed,0);

 INSERT INTO Test1 (name) VALUES('Test')

In the above result system started storing identity from "0" instead of "1"

 DELETE FROM Test1

EXEC DBCC CHECKIDENT ('Test1',reseed,0);

INSERT INTO Test1 (name) VALUES('Test')

To correct the issue verify the last value first based on that reseed the data

Declare @lastValue int

SELECT @lastValue = CONVERT(INT,last_value )
FROM sys.identity_columns ic WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON ic.OBJECT_ID = o.OBJECT_ID
WHERE o.name = 'Test1'

IF @lastValue IS NOT NULL
  EXEC DBCC CHECKIDENT ('Test',reseed,0);