In daily SQL Server development, the identity column of the Identity type is often used as the self-growing number of a table structure. For example, article number, record serial number, etc. References to self-growing identified columns greatly facilitate the development of database programs, but sometimes this stubborn field type can also cause some trouble.
1. Modify the field values of the identification column:
Sometimes, in order to implement a function, it is necessary to modify the value of a field of type Identity self-growing, but this operation is not allowed by default due to the type of identity column. For example, if there are 5 pieces of data added normally in the database, and 2 are deleted at this time, then if the data is added again, the self-growing identification column will be automatically assigned a value of 6, but if you want to assign a value of 3 when inserting data, it is not allowed by default. If you want to change the value of this field, you can completely control the insertion of the value of the identification field, there are still methods, haha.
SET IDENTITY_INSERT /[TABLE/] [ON| OFF]
Using the above statement, you can easily control whether a self-growing identifier column in a table will automatically grow, that is, whether you are allowed to manually specify the value of the identifier column field when inserting a record. If specified as on, you can specify the value of the identifying column field when inserting, which does not automatically grow the assigned value. Of course, if you use it up, you need to use this statement to turn off the switch to the default state off, otherwise the field will not automatically increase the assigned value when you insert data next time.
2. Reset the field value of the identification column:
When a part of the data record is deleted, the new data record added later, the value of the identification column will have a large idle interval, which looks very unpleasant. Even if you delete all records in the table, the value of the identity column will continue to increase automatically and increase endlessly, rather than growing from scratch. The seed value of the self-growing field can be reset by using the following statement:
DBCC CHECKIDENT（TABLE, [RESEED| NORESEED], [1])
The above statement will force the seed value of the specified table to be reset to 1. However, if you don't want to reset the seed to 1, you can replace the third parameter with the seed value you want to use. If you want to know the current seed instead of resetting the identified seed, you need to use NORESEED instead of setting the third parameter. |
Posted on 8/23/2018 12:20:44 PM
|
|
|
|
Landlord|
Posted on 8/23/2018 12:57:08 PM
|
