如何更新SQL Server中的标识列？

Question

更改或重置此栏目的最佳方法是什么？

Answer 1

不能更新标识列。

SQL Server不允许更新identity列，这与您可以使用update语句对其他列进行的操作不同。

但也有一些替代方案来实现类似的要求。

null

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

null

SET IDENTITY_INSERT YourTable {ON|OFF}

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF