How to operate the SQL Server identity column

In SQL Server, the identity column is often used. This self-increasing field operation is indeed more convenient. But it sometimes brings some trouble.

Example 1: when some data is deleted from a table, the number of the auto-increment column is no longer a series of connections. In this case, we can use the following solutions.

Set identity_insert [Table] [ON | Off]

Explicit values can be inserted into the table's ID column. When it is set to on, the number inserted to the ID column may be manually specified during the insert operation. After the operation is complete, restore identity_insert to off. Otherwise, the number must be specified during the next insert, otherwise the insert operation cannot be completed.

Example 2: When all the records in the table are deleted, but the value of the ID column is getting bigger and bigger, if it is not reset, it will continue to grow endlessly. At this time, we will use:

DBCC checkident (table, [reseed | noreseed], [1])

The seed value of the specified table is forcibly reset to 1. However, you may not want to reset the seed to 1. In this case, you can replace the third parameter with the seed value you want. Sometimes you may want to know the current seed, instead of resetting the seed. You need to use noreseed instead of worrying about the third parameter.