SQL Server database auto-increment ID column update and Modification Operation Method

In daily SQL Server development, identity columns of the Identity type are often used as the auto-increment numbers of a table structure. For exampleArticleNumber, record number, and so on. The reference of the Self-increasing ID column greatly facilitates the databaseProgramDevelopment, but sometimes this stubborn field type also brings some trouble.

1. Modify the field value of the ID column:

Sometimes, to implement a certain function, you need to modify the value of a field of the Self-increasing type of identity, but this operation is not allowed by default due to the type of the identity column. For example, if there are currently five normal data records added to the database and two data records are deleted at this time, the self-increasing ID column will be automatically assigned a value of 6 when the data is added, however, if you want to assign 3 values when inserting data, it is not allowed by default. If you want to change the value of this field and completely control the insertion of the value of this field, there are still some methods, haha.

Set identity_insert/[Table/] [ON | Off]
Using the preceding statement, you can easily control whether a table's auto-increment ID column automatically grows, that is, whether to allow you to manually specify the value of the column field when inserting a record. If on is specified, you can specify the value of the field that identifies the column during insert. This value does not automatically increase the value. Of course, if it is used up, you still need to use this statement to turn off the switch to the default status off. Otherwise, this field will not automatically increase the value next time you insert data.

2. Reset the field value of the ID column:

When a part of a data record is deleted and a new data record is added later, the value of the ID column will have a large idle interval. Does it seem uncomfortable. Even if you delete all records in the table, the value of the identity column will automatically increase endlessly, instead of increasing from the beginning. The following statement resets the seed value of the auto-increment field:

DBCC checkident (table, [reseed | noreseed], [1])
The preceding statement forcibly resets the seed value of the specified table to 1. However, if you do not want to reset the seed value to 1, you can replace the third parameter with the seed value you want. If you want to know the current seed, instead of resetting the seed, you need to use noreseed instead of setting the third parameter.