身份列插入失败 [英] Identity Columns Failed Inserts

问题描述

大家好，我有一个简单的问题:

当插入语句在标识列上失败时会发生什么?

例如，是否可以说如果我插入带有标识列的行，则该标识列将为1，然后再次插入，但失败并且不插入和数据.然后尝试再次插入，该行的标识现在为3?

任何建议将不胜感激.

Thsnks.

Hi guys I just have one quick question:

what happens when an insert statement fails on an identity column?

Is it possible that say for example that if I insert a row with an identity column, that identity column will be 1, and insert again but that fails and does not insert and data. Then try to insert again and that identity for that row is now 3?

Any advice will be much appreciated.

Thsnks.

推荐答案

根据msdn 文档 [ ^ ]，失败了交易仍会导致数字增加.

失败的语句和事务可能会更改表的当前标识，并在标识列值中造成空白.即使未提交尝试将值插入表中的事务，也不会回滚标识值.例如，如果INSERT语句由于违反IGNORE_DUP_KEY而失败，该表的当前标识值仍在增加."

因此，您总是有机会错过某些值.

As per msdn documentation[^], a failed transaction can still cause the number to be incremented.

"Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented."

As a result, you always have a chance that some values may be missed.

这篇关于身份列插入失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！