Adjust the current initial value of the auto-increment field in SQL Server

A few days ago, when I upgraded the database of a Communtiy Server from SQL 2000 to SQL 2005, I encountered a strange problem and reported the following error:

Violation of primary key constraint 'pk _ cs_threads'. Cannot insert duplicate key in object 'dbo. cs_threads '.

After the analysis, we found that the table's auto-increment field database has reached 6144, and the initial auto-increment value of the table maintained by the database is only 6109.

The solution is simple. You can use the following SQL statement:

Dbcc checkident ('cs _ Threads ')

The preceding statement indicates that if the current ID value of 'cs _ Threads' in the table is smaller than the maximum id value stored in the column, it is reset by the maximum value in the column.

The CHECKIDENT command can be written in the following ways:

1. 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.

Similar to the following report:

Checking identity information: current identity value '20160301', current column value '20160301 '.

2. 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.

When the preceding command is executed, a report similar to the preceding report is reported.

3. dbcc checkident ('table _ name', RESEED, new_reseed_value)

The current value is set to new_reseed_value.

If the row is not inserted into the table after the table is created, new_reseed_value is used as the ID of the first row inserted after dbcc checkident is executed. Otherwise, new_reseed_value + 1 is used for the next inserted row.

If the value of new_reseed_value is smaller than the maximum value in the ID column, error 2627 will be generated when the table is referenced later.