Adjust the current initial value of the self-added field in SQL Server _mssql

A few days ago, when I upgraded a communtiy Server database from SQL 2000 to SQL 2005, I ran into a weird problem that 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 self-increasing field database has reached 6144, and the database maintained that the table's initial self-appreciation only to 6109.

The workaround is simple: Use the following SQL statement to fix the

DBCC checkident (' cs_threads ')

The above statement means that if the current identity value of table ' Cs_threads ' is less than the maximum identity value stored in the column, the maximum value in the identity column is used to reset it.

Checkident commands can be written in the following ways:

1. DBCC checkident (' table_name ', noreseed)

The current identity value is not reset. DBCC Checkident Returns a report that indicates the current identity value and the expected identity value.

A report similar to the following:

Checking Identity Information:current Identity value ' 6109 ', current column value ' 6144 '.

2. DBCC CHECKIDENT (' table_name ') or DBCC CHECKIDENT (' table_name ', reseed)

If the table's current identity value is less than the maximum identity value stored in the column, the maximum value in the identity column is used to reset it.

When the above order is executed, a report similar to the above will be reported.

3. DBCC checkident (' table_name ', reseed, New_reseed_value)

The current value is set to New_reseed_value.

If a row has not been inserted into the table since it was created, the first row inserted after the DBCC checkident is executed uses new_reseed_value as the identity. Otherwise, the next inserted row will use New_reseed_value + 1.

If the value of new_reseed_value is less than the maximum value in the identity column, a No. 2627 error message will be generated when the table is referenced later.