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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.