Manually set the start seed DBCC checkident ('dbo. t_ticket_info ', reseed, 16652) of the auto-increment column)

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,

Fig GIF(9.33 K) 2/1/2009 3:49:03 pm

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 the current value of 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

2009-02-00000154735.gif(6.04 K) 2/1/2009 3:49:03 pm

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.