SQL Server - Reset the numeric counter (seed) of an Identity column

Ryan

5 years ago

If you came across this article, you probably need to reset the numeric value (seed counter) of an identity column used by a table in your SQL Server database.

The method described here works with all versions of SQL Server : SQL Server 2002, SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, up to the most recent SQL Server 2017.

DBCC CHECKIDENT   
 (   
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]

DBCC CHECKIDENT

(

table_name

[, { NORESEED | { RESEED [, new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

Arguments

table_name: Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must follow the rules for identifiers. Two or three part names must be delimited, such as 'Person.AddressType' or [Person.AddressType].
NORESEED: Specifies that the current identity value shouldn't be changed.
RESEED: Specifies that the current identity value should be changed.
new_reseed_value: Is the new value to use as the current value of the identity column.
WITH NO_INFOMSGS: Suppresses all informational messages.

Example

For example, the following command:

DBCC CHECKIDENT(Table1,RESEED,1);

1	DBCC CHECKIDENT(Table1,RESEED,1);

Will reset to 1 the identity column value of Table1. Once executed, the command will output the following confirmation message:

Identity information control: current Identity value '1', current column value '1. DBCC execution completed.
If DBCC error messages were displayed, contact your system administrator.

References

If you need additional info, you can also take a look to the dedicated DBCC CHECKIDENT page available on the official SQL Server 2017 documentation.

Print Friendly & PDF Download