Talking about the operation method of SQL Server identity column

The Identity identity column is often used in SQL Server, and this growing field is indeed convenient to operate. But sometimes it can bring some trouble.

Example one: When some data is deleted from the table, the number from the growth column is no longer a line sequence. This time we can use the following solutions to solve.

SET IDENTITY_INSERT [TABLE] [ON|OFF]

Allows explicit values to be inserted into the identity column of the table, and when set to on, the number inserted into the identity column may be manually specified at the time of the insert operation, and the identity_insert must be restored to off after the operation completes, otherwise the number must be specified the next time it is inserted. Otherwise, you cannot complete the insert operation.

Example two: When the records in the table are all deleted, but when the value of the identity column becomes larger, it will grow endlessly if it is not reset. This time we are going to use:

DBCC CHECKIDENT（TABLE, [RESEED|NORESEED], [1])

The seed value of the specified table will be forcibly reset to 1. However, you may not want to reset the seed to 1, in which case you can replace the third parameter with the seed value you want to use. Sometimes you may want to know the current seed, not to reset the seed, then you need to use the noreseed, without having to worry about the third parameter.