sql - 重新获得失去的身份值(value)

Question

如果语句在事务 block 内失败，是否可以重新创建 SQL Server 表的标识值？

请仔细阅读以下代码:

DECLARE @IdentityTable AS TABLE (ID INT IDENTITY(1, 1), Description VARCHAR(50))

INSERT INTO @IdentityTable (Description) 
VALUES('Test1')

BEGIN TRY 
BEGIN TRANSACTION IdentityTest
    INSERT INTO @IdentityTable (Description) 
    VALUES('Test2')

    INSERT INTO @IdentityTable (Description) 
    VALUES(1/0)

    COMMIT TRANSACTION IdentityTest
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION IdentityTest
END CATCH

INSERT INTO @IdentityTable (Description) 
VALUES('Test4')

SELECT * FROM @IdentityTable

由于ROLLBACK TRANSACTION，第 3 号身份丢失。还能恢复吗？

Answer 1

您正在尝试使用IDENTITY属性来生成连续的数字并维护它；这不是 IDENTITY 的用途。它旨在根据当前种子提供递增值(就其本身而言(没有 PRIMARY KEY 约束或 UNIQUE INDEX)，它甚至不保证唯一性，因为种子可以更改(感谢 HoneyBadger 这么早就提醒我))。

如果 INSERT 失败，IDENTITY 的值仍会递增。另外，如果您要从表中DELETE一行，这不会导致每个“后面”行的 ID 相应更新；因此你也会有一个差距。

确保获得递增值的唯一有保证的方法是在运行时使用类似ROW_NUMBER的函数。例如:

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS cID,
       Description
FROM YourTable;

Remarks文档的部分明确指出连续值不得到保证:

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

...

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.