TSQL 更新时自动递增
SQL Server 2008+
我有一个带有自动增量列的表，我希望不仅在插入时增量，而且在更新时也增量。该列不是主键，但还有一个主键，它是通过 newid() 自动创建的 GUID。
据我所知，有两种方法可以做到这一点。
1.) 删除现有行并插入具有相同值（加上任何更新）的新行。
或
2.) 更新现有行并使用以下命令获取“下一个”标识值：
IDENT_CURRENT('myTable') + IDENT_INCR('myTable')
无论哪种情况，我都被迫允许标识插入。 （使用选项 1，因为表的主键需要保持相同，使用选项 2，因为我正在使用特定值更新自动增量列。）我不确定锁定/性能的后果是什么这是。
对此有什么想法吗？有更好的方法吗？这里的目标是每当插入或更新行时在列中维护一组始终增加的整数值。
SQL Server 2008+
I have a table with an auto-increment column which I would like to have increment not only on insert but also update. This column is not the primary key, but there is also a primary key which is a GUID created automatically via newid().
As far as I can tell, there are two ways to do this.
1.) Delete the existing row and insert a new row with indentical values (plus any updates).
or
2.) Update the existing row and use the following to get the "next" identity value:
IDENT_CURRENT('myTable') + IDENT_INCR('myTable')
In either case, I'm forced to allow identity inserts. (With option 1, because the primary key for the table needs to remain the same, and with option 2 because I'm updating the auto-increment column with a specific value.) I'm not sure what the locking/performance consequences of this are.
Any thoughts on this? Is there a better approach? The goal here is to maintain an always increasing set of integer values in the column whenever a row is inserted or updated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为类型为 rowversion （以前称为“timestamp”） 的列可能是您最简单的选择，尽管在 8 字节时这些可能相当于相当大的整数。 “timestamp”语法已被弃用，取而代之的是 rowversion（因为 ISO SQL 具有时间戳数据类型）。
如果您继续使用标识列方法，您可能希望将逻辑放入 UPDATE 触发器中，这将有效地将 UPDATE 替换为您所描述的 INSERT 和 DELETE 组合。
请注意，Identity 列值不保证是连续的，只能递增。
I think a column of type rowversion (formerly known as "timestamp") might be your simplest choice, although at 8 bytes these can amount to fairly large integers. The "timestamp" syntax is deprecated in favor of rowversion (since ISO SQL has a timestamp datatype).
If you stay with the Identity column approach, you would probably want to put your logic into an UPDATE trigger, which would effectively replace the UPDATE with the INSERT and DELETE combination you've described.
Note that Identity column values are not guaranteed to be sequential, only increasing.
它必须是整数列吗？时间戳列将为您提供您正在寻找的开箱即用的功能。
Does it need to be an integer column? A timestamp column will provide you the functionality you are looking for out of the box.
无法更新具有标识属性的列。一旦为具有标识属性的列分配了一个值（无论是自动分配还是在启用
identity_insert的情况下），它就是一个不变值。此外，身份属性可能无法通过alter columns禁用或删除。我相信您想要查看的是 SQL Server
TIMESTAMP（现在在 SQL Server 2008 中称为rowversion）。它本质上是一个自动递增的二进制值。每个数据库都有一个唯一的 rowversion 计数器。具有时间戳/行版本列的表中的每一行插入/更新都会导致计数器被标记，并将新值分配给插入/修改的行。Columns with an identity property can't be updated. Once the column with an identity property on it has been assigned a value, either automatically, or with
identity_inserton, it is an invariant value. Further the identity property may not be disabled or removed viaalter column.I believe what you want to look at is a SQL Server
TIMESTAMP(now calledrowversionin SQL Server 2008). It is fundamentally an auto-incrementing binary value. Each database has a unique rowversion counter. Each row insert/update in a table with a timestamp/rowversion column results in the counter being ticked up and the new value assigned to the inserted/modified row.