SQL Server - 如何在更新时生成唯一的序列号
我的表格中有一列，例如 updateStamp。我想要一种在行更新时使用新的序列号更新该字段的方法。
数据库的流量很大，大部分是读取，但也可能批量发生多个并发更新。因此，该解决方案应该导致最少的锁定。
提出此要求的原因是，我需要为客户端提供一个解决方案来向前迭代表，并且如果更新了一行，它应该再次出现在结果集上。
因此，查询就像
SELECT *
FROM mytable
WHERE updateStamp > @lastReturnedUpdateStamp
ORDER BY updateStamp
不幸的是时间戳在这里不起作用，因为多个更新可能同时发生。
I have column in my table, say updateStamp. I'd like to get an approach to update that field with a new sequential number upon row update.
The database has lot of traffic, mostly read, but multiple concurrent updates could also happen in batches. Therefore the solution should cause minimal locks.
Reason for this requirement is that I need to have a solution for clients to iterate over the table forwards and if a row is updated - it should come up on the result set again.
So, query would then be like
SELECT *
FROM mytable
WHERE updateStamp > @lastReturnedUpdateStamp
ORDER BY updateStamp
Unfortunately timestamps do not work here because multiple updates could happen at same time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
timestamp（已弃用）或rowversion（当前）数据类型是我所知道的唯一一种在行上的每次写入操作时都会更新的数据类型。它本身不是时间戳 - 它不存储日期、时间（以小时、秒为单位）等 - 它实际上更像是一个
RowVersion（因此名称更改） - 一个独特的、不断增加的数字（二进制）在行上。它通常用于检查从读取行到更新行之间的任何修改。
由于它并不是真正的日期/时间信息，因此您很可能必须为人类可读的信息设置另一列。您可以向表中添加
LastModified DATETIME列，并使用DEFAULT GETDATE()约束，您可以在插入时插入新值。为了保持最新，您必须编写一个 AFTER UPDATE 触发器，以便在发生任何更新时更新LastModified列。SQL Server 2011（又名“Denali”）将为我们带来
SEQUENCES这将非常适合您的情况 - 但可惜，距离正式发布至少还需要一年......The
timestamp(deprecated) orrowversion(current) data type is the only one I'm aware of that is updated on every write operation on the row.It's not a time stamp per se - it doesn't store date, time in hours, seconds etc. - it's really more of a
RowVersion(hence the name change) - a unique, ever-increasing number (binary) on the row.It's typically used to check for any modifications between the time you have read the row, and the time you're going to update it.
Since it's not really a date/time information, you will most likely have to have another column for that human-readable information. You can add a
LastModified DATETIMEcolumn to your table, and with aDEFAULT GETDATE()constraint, you can insert a new value upon insertion. For keeping that up to date, you'll have to write a AFTER UPDATE trigger to update theLastModifiedcolumn when any update occurs.SQL Server 2011 (a.k.a. "Denali") will bring us
SEQUENCESwhich would be the perfect fit in your case here - but alas, that' still at least a year from official release.....