Reset the ID of the auto-incrementing primary key in the SQL Server database table

You can use DBCC checkident to reset the start value of the identity column of the table in use;

For example, if you want to delete all records in a table, you can run DBCC checkident (tablename, reseed, 0) in the query analyzer when the ID column starts from 1. The usage of this statement is as follows: DBCC checkident

Check the current Identifier value of the specified table. If necessary, correct the Identifier value.

Syntax

DBCC checkident
('Table_name'
[,{Noreseed
| {Reseed [, New_reseed_value]}
}
]
)

Parameters

'Table_name'

Is the name of the table whose current identity value is to be checked. The table name must comply with the identifier rules. For more information, see use identifiers. The specified table must contain an ID column.

Noreseed

Specifies that the current ID value should not be corrected.

Reseed

Specifies that the current ID value should be corrected.

New_reseed_value

Is the value to be used when the value is re-assigned in the ID column.

Note

If necessary, DBCC checkident will correct the current ID value of the column. However, if the ID column is created using the not for replication clause (in the create table or alter table statement), the current ID value is not corrected.

If the primary key or unique key constraint exists in the ID column, invalid ID information may cause error 2627.

The specific correction to the current Identifier value depends on the parameter specification.

DBCC checkident statement	Id correction
DBCC checkident ('Table_name', Noreseed)	The current ID value is not reset. DBCC checkident returns a report that specifies the current ID value and the expected id value.
DBCC checkident ('Table_name') Or DBCC checkident ('Table_name', Reseed)	If the current ID value of the table is smaller than the maximum id value stored in the column, reset it with the maximum value in the column.
DBCC checkident ('Table_name', Reseed,New_reseed_value)	The current value is setNew_reseed_value. If the row is not inserted into the table after the table is created, the first row inserted after DBCC checkident is executed will useNew_reseed_valueAs the identifier. Otherwise, the next inserted row usesNew_reseed_value+ 1. IfNew_reseed_valueThe value of is less than the maximum value in the identification column. When you reference the table later, error 2627 is generated.

The current ID value can be greater than the maximum value in the table. In this case, DBCC checkident does not automatically reset the current ID value. To reset the current ID value when the current ID value is greater than the maximum value in the column, use either of the following methods:

• Run DBCC checkident ('Table_name', Noreseed) to determine the current maximum value in the column, and then use DBCC checkident ('Table_name', Reseed,New_reseed_value) Statement to specify this valueNew_reseed_value.

• SetNew_reseed_valueTo execute DBCC checkident ('Table_name', Reseed,New_reseed_value), And then run DBCC checkident ('Table_name', Reseed ).

Result set

Whether or not any options are specified (for tables that contain the ID column; the following example usesPubsDatabaseJobsTable), DBCC checkident returns the following result set (the value may change ):

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

Permission

DBCC checkident permission is granted to the table owner by default,SysAdminFixed server roles andDb_ownerMembers of a fixed database role cannot be transferred.

Example A. Reset the current ID value if necessary

The following example is reset if necessary.JobsThe current Identifier value of the table.

USE pubsGODBCC CHECKIDENT (jobs)GO

B. report the current ID value

Example reportJobsThe current ID value in the table. If the id value is incorrect, it is not corrected.

USE pubsGODBCC CHECKIDENT (jobs, NORESEED)GO

C. Force the current ID value to 30

Force the following exampleJobsThe current ID value in the table is 30.

USE pubsGODBCC CHECKIDENT (jobs, RESEED, 30)GO