When SQL Server enters data, it can be maintained with the ident_current and Checkident commands when it encounters a primary key ID that needs to be maintained.
ident_currentThe command returns the last identity value generated by the specified table or view. Use the following:
ident_current (' table_name ')Returns the type numeric (38,0).
DBCC checkidentcommand is used to check or specify the identity value of the current table/view. Use the following:
DBCC checkident
(
table_name
[, {noreseed | {reseed [, New_reseed_value]}} ]
)
[with NO_INFOSGS]
in general, we use the following:1) View and correct the current identity value if necessary: DBCC checkident (' table_name ') 2) View the current identity value, even if there is an error, no view correction:DBCC checkident (' table_name ', noreseed)3) Specify a new identity value:DBCC checkident (' table_name ', Noreseed, new_reseed_value)With NO_INFOSGS is a closed message prompt. --------------------------------------------------
issues to be aware of:When the New_reseed_value setting is less than the value of the primary key ID in the table, the following error occurs during subsequent insertions: a) When a primary key or a unique constraint exists, a 2627 error is reported because the current primary key value is in the data table, resulting in a conflict; b) when the primary KEY or UNIQUE constraint does not exist, Duplicate primary key error is reported.
Workaround:Use SELECT Max (ID) from table_name, query out the maximum ID in the table, and then reset a new_reseed_value, or if the data in the table has been deleted, you can use DBCC CHECKIDENT (' Table_ Name ', Noreseed, 1) is set to 1 and is then corrected with DBCC CHECKIDEN (' table_name '). Also, in SQL, if you want to insert an ID, you can typically insert a value 1 more than the current sequence value with select Ident_current (' table_name ') +. Reference: Https://msdn.microsoft.com/en-us/library/ms176057.aspx https://msdn.microsoft.com/en-us/library/ms175098.asp X
SQL Server database DBCC CHECKIDENT command introduction
