Problem with reseeding IDENTITY column in a SQL Server 2008 database table

Problem that we've just discovered with DBCC CHECKIDENT ('MyTable', reseed, 1) which doesn't reset the identity seed to 1 if the table has had data in it before, instead the newly inserted rows will start from ID = 2.

Example below…

begin tran

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MerrickTest]') AND type in (N'U'))

DROP TABLE [dbo].[MerrickTest]

CREATE TABLE [dbo].[MerrickTest](

            [ID] [int] IDENTITY(1,1) NOT NULL,

            [Value] [varchar](50) NULL,

CONSTRAINT [PK_MerrickTest] PRIMARY KEY CLUSTERED

(

            [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

insert into merricktest (value) values ( 'test' )

select * from merricktest

delete from MerrickTest

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

DBCC CHECKIDENT (merricktest, reseed, 1)

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

insert into merricktest (value) values ( 'test' )

select * from merricktest -- note that the value in the ID column is now 2 not 1

drop table MerrickTest

rollback

Solution...

One solution you could use for this is to check the last update on the database table, and then reseed from zero if it has been updated in the past using the following sql

Select object_name(object_id), last_user_update from sys.dm_db_index_usage_stats where object_name(object_id) like 'MerrickTest'