I have a Microsoft SQl server 2005 database with a table whose primary key has the identity specification is set to yes to auto increment.
我有一个Microsoft SQl服务器2005数据库,其中一个表的主键具有标识规范设置为yes以自动递增。
Recently the primary key skipped two numbers(which I understand is normal as they are not necessarily sequential).
最近主键跳过两个数字(我理解这是正常的,因为它们不一定是顺序的)。
However I would like to find out, if possible, why they skipped and when, i.e. if a stored procedure rollback occured and the primary key sequence didn't rollback or they were deleted somehow.
但是,我想知道,如果可能的话,为什么他们跳过了,什么时候跳过,即如果发生存储过程回滚并且主键序列没有回滚或者它们以某种方式被删除。
My question is, does Microsoft SQL server management studio have a designated area that stored records such as this, i.e. transaction logs etc for me to have a look at to try and determine why this skip happened.
我的问题是,Microsoft SQL服务器管理工作室是否有一个指定区域,存储此类记录,即事务日志等,以便我查看以尝试确定此跳过发生的原因。
2 个解决方案
#1
1
Unless your code log it there is no record. So no: SQL Server does not take note of this.
除非您的代码记录它,否则没有记录。所以没有:SQL Server没有注意到这一点。
A transaction rollback or an INSERT error (which is a single statement rollback) will generate gaps in the number sequence. SQL Server does not take note of this.
事务回滚或INSERT错误(单个语句回滚)将在数字序列中生成间隙。 SQL Server没有注意到这一点。
#2
2
Assuming you have an IDENTITY that increments by 1 each time, then you will see haps in your ids if a ROLLBACK occurs - in the event of a rollback, the IDENTITY value will not be reverted back so the ID value it assigned for that operation will essentially be lost. This is entirely normal behaviour.
假设您每次都有一个增加1的IDENTITY,那么如果发生ROLLBACK,您将看到id中的haps - 如果发生回滚,IDENTITY值将不会被还原,因此为该操作分配的ID值将为基本上是迷路了。这完全是正常行为。
Suppose you have a high volume of INSERTs, in the event of one insert failing/rolling back, then rolling back the IDENTITY value increment would be a nightmare as there could have been a whole swathe of INSERTs in the mean time.
假设你有大量的INSERT,如果一个插入失败/回滚,那么回滚IDENTITY值增加将是一场噩梦,因为同时可能有一大堆INSERT。
#1
1
Unless your code log it there is no record. So no: SQL Server does not take note of this.
除非您的代码记录它,否则没有记录。所以没有:SQL Server没有注意到这一点。
A transaction rollback or an INSERT error (which is a single statement rollback) will generate gaps in the number sequence. SQL Server does not take note of this.
事务回滚或INSERT错误(单个语句回滚)将在数字序列中生成间隙。 SQL Server没有注意到这一点。
#2
2
Assuming you have an IDENTITY that increments by 1 each time, then you will see haps in your ids if a ROLLBACK occurs - in the event of a rollback, the IDENTITY value will not be reverted back so the ID value it assigned for that operation will essentially be lost. This is entirely normal behaviour.
假设您每次都有一个增加1的IDENTITY,那么如果发生ROLLBACK,您将看到id中的haps - 如果发生回滚,IDENTITY值将不会被还原,因此为该操作分配的ID值将为基本上是迷路了。这完全是正常行为。
Suppose you have a high volume of INSERTs, in the event of one insert failing/rolling back, then rolling back the IDENTITY value increment would be a nightmare as there could have been a whole swathe of INSERTs in the mean time.
假设你有大量的INSERT,如果一个插入失败/回滚,那么回滚IDENTITY值增加将是一场噩梦,因为同时可能有一大堆INSERT。