Today, I encountered the following problem when studying SQL Server 2005: In the exercise, I created a table "person" with five fields, set the ID to the primary key, and set it to auto-increment,
After data is inserted for multiple times, the auto-increment ID is chaotic, and after the data is deleted, the auto-increment ID does not return to zero, which is checked by multiple parties, there are two ways to control auto-increment fields:
Method 1: If no data is needed, you can directly clear all data and restore the auto-increment field to count from 1.
Truncate table name
Method 2: Set DBCC checkident (''table _ name'', reseed, new_reseed_value) 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.
Method 2 does not clear existing data, and the operation is flexible. It can not only return the auto-increment value to zero, but also apply to deleting a large number of consecutive rows, resetting the auto-increment value and inserting new data; or starting from a new value, of course, it cannot conflict with an existing one.
When there is data in the table
When DBCC checkident (''dbo. Person '', reseed, 10) is executed and data is added through management Studio, the ID starts from 11 automatically, that is, new_reseed_value + 1.
ArticleSource: http://www.diybl.com/course/7_databases/ SQL /sqlServer/200838/103319.html