为什么SQL Server跳过标识列值 [英] Why SQL Server is skipping identity column values

问题描述

我对SQL Server的标识"列属性有一个奇怪的经历.
我有一个带有标识列的表.今天早上，我注意到SQL Server跳过了该列中的值.
现在的值就像
101
102
103
105

我在这里没有看到104.

我通过在插入数据时创建异常来重新生成此问题.每当发生异常时，SQL都会将其视为插入，并在下一次插入尝试时跳过该值.

我该如何解决?我希望此标识列值是连续的.

谢谢您的时间
Sebastian

Hi,

I had a strange experience with SQL Server''s Identity column property.
I have a table with an identity column. This morning I noticed that SQL server skipped a value in that column.
Now the values are like
101
102
103
105

I am not seeing 104 here.

I have regenerated this issue by creating an exception while inserting data. whenever an exception occurs, SQL considers that as an insertion and skips that value in the next insertion attempt.

How I can solve this? I want this identity column value be sequential.

Thanks for your time
Sebastian

推荐答案

您必须在插入操作周围添加异常处理.
如果发生异常，则必须重新设置标识值
dbcc checkident (mytable, reseed, 30)
最后一个标识值
您将在这里获得更多资源

种子身份值
http://ryanfarley.com/blog/archive/2004/12/19/1313.aspx [^ ]

检索身份值
http://www.blackwasp.co.uk/SQLScopeIdentity.aspx [

Hi,
you have to add exception handling around insert operation.
if exception occured you have to reseed the identity value
dbcc checkident (mytable, reseed, 30)
with the last identity value
You will get more resource in here

Reseed identity value
http://ryanfarley.com/blog/archive/2004/12/19/1313.aspx[^]

Retrieve identity value
http://www.blackwasp.co.uk/SQLScopeIdentity.aspx[^]

验证所有插入所需的值在尝试插入语句本身并正常退出过程之前，而不是仅对插入异常进行操作.
同样，您也许可以将流程包装在事务中.我不太确定，但是在这种情况下，事务回滚可能会允许自动标识值重设.

Validate all of the values necessary for insertion before attempting the insert statement itself and exit the process gracefully instead of only doing it on an insert exception.
As well, you may be able to wrap the process in a transaction. I''m not totally sure, but a transaction rollback may allow for the auto identity value to reset itself in that case.

这篇关于为什么SQL Server跳过标识列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！