CODEWITHSUNDEEP
sqlsql-servert-sqldbcc
awj
awj

Reputation: 7959

Reset Identity Seed ('DBCC CHECKIDENT ... RESEED' is not working)

For my unit tests I have an automated process which builds a copy of a DB, table by table. I first of all run a SELECT INTO and add the primary key and any indices.

Some of the tables I copy with data, and for those without data I wish to reset the identity seed.

To achieve this I have the following T-SQL statement:

EXEC('TRUNCATE TABLE [' + @TableName + '];')
IF EXISTS (SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID(@TableName) AND last_value IS NOT NULL)
BEGIN
    EXEC('DBCC CHECKIDENT (' + @TableName + ', reseed, 1)')
END

The query runs without any reported problems but when I inspect the table in design view the Identity Seed value is never reset.

Can anyone please tell me why the Identity Seed is not being reset, or what else I need to do?

UPDATE: As per some comments I have modified the sys.identity_columns to the following:

IF EXISTS (SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID(@TableName))

When run, this returns the following message:

Checking identity information: current identity value '1'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Which looks promising. However, if I then refresh the table (in the Object Explorer) and look at it in the design view, the Identity Seed is unchanged, i.e. not 1 (or 0) but 135.

Upvotes: 3

Views: 6270

Answers (1)

Jeff
Jeff

Reputation: 8148

Ran into a similar problem assigning the identity key (in my case to a larger value). In my case I found that if I ran DBCC CHECKIDENT (TableName) after running the DBCHECKIDENT RESEED it would then fully "refresh" itself into the new value. Otherwise it will get stuck on an older value. I don't know why, but it worked in my case (and may be useful to someone else.)

Upvotes: 3

Related Questions