Learn DBCC checkident

Source SQL books online

Check the current Identifier value of the specified table. If necessary, correct the Identifier value.

Syntax
DBCC checkident
('Table _ name'
[, {Noreseed
| {Reseed [, new_reseed_value]}
}
]
)

If the primary key or unique key constraint exists in the column,
Use DBCC checkident ('table _ name', reseed, new_reseed_value) to set the current value to new_reseed_value. Note the following when using this parameter:The value of new_reseed_value should take the maximum value in the current ID column to avoid insertion errors.

Example
A. Reset the current ID value if necessary
In the following example, the current identity value of the jobs table is reset if necessary.

Use pubs
Go
DBCC checkident (jobs)
Go

B. report the current ID value
The following example shows the current identity value in the jobs table. If the identity value is incorrect, it is not corrected.

Use pubs
Go
DBCC checkident (jobs, noreseed)
Go

C. Force the current ID value to 30
In the following example, the current ID value of the mandatory jobs table is 30.

Use pubs
Go
DBCC checkident (jobs, reseed, 30)
Go

PS:
In the example of local Experiment C, the current ID has a maximum value of 14. After modification, legal data is inserted and job_id = 31 is added;

If DBCC checkident (jobs, reseed, 30) is executed and data is inserted at this time, an error occurs:
Primary key constraint failure error. Invalid ID information may cause error 2627.