SET IDENTITY_INSERT 和 DBCC CHECKIDENT

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table.

 

Remarks

 At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.  

 

DBCC CHECKIDENT (Transact-SQL)

Checks the current identity value for the specified table in SQL Server 2017 and, if it is needed, changes the identity value.

You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

可以使用这个功能进行reseed,也可以用来检查current identify value

 

作者:Chuck Lu    GitHub