SQL Server – Reset the numeric counter (seed) of an Identity column How to reset the numeric value of the seed counter of an identity column with SQL Server 2002, 2005, 2008, 2008R2, 2012, 2016, 2017, 2019

SQL Server - Recuperare il Product Key da una installazione esistente

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.

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:

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.