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.
我的表中有列,比如updateStamp。我希望得到一种方法,在行更新时使用新的序列号更新该字段。
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.
不幸的是,时间戳在这里不起作用,因为多个更新可能同时发生。
1 个解决方案
#1
2
The timestamp (deprecated) or rowversion (current) data type is the only one I'm aware of that is updated on every write operation on the row.
时间戳(不建议使用)或rowversion(当前)数据类型是我所知道的唯一一个在行上每次写入操作时更新的数据类型。
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.
它本身不是时间戳 - 它不存储日期,时间以小时,秒等表示 - 它实际上更像是一个RowVersion(因此名称更改) - 行上唯一的,不断增加的数字(二进制)。
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 DATETIME column to your table, and with a DEFAULT 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 the LastModified column when any update occurs.
由于它不是真正的日期/时间信息,因此您很可能必须为该人类可读信息设置另一列。您可以向表中添加LastModified DATETIME列,并使用DEFAULT GETDATE()约束,可以在插入时插入新值。为了使其保持最新,您必须编写AFTER UPDATE触发器以在发生任何更新时更新LastModified列。
SQL Server 2011 (a.k.a. "Denali") will bring us SEQUENCES which would be the perfect fit in your case here - but alas, that' still at least a year from official release.....
SQL Server 2011(a.k.a。“Denali”)将为我们带来SEQUENCES,这将完全适合您的情况 - 但是,唉,至少还有一年的官方发布.....
#1
2
The timestamp (deprecated) or rowversion (current) data type is the only one I'm aware of that is updated on every write operation on the row.
时间戳(不建议使用)或rowversion(当前)数据类型是我所知道的唯一一个在行上每次写入操作时更新的数据类型。
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.
它本身不是时间戳 - 它不存储日期,时间以小时,秒等表示 - 它实际上更像是一个RowVersion(因此名称更改) - 行上唯一的,不断增加的数字(二进制)。
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 DATETIME column to your table, and with a DEFAULT 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 the LastModified column when any update occurs.
由于它不是真正的日期/时间信息,因此您很可能必须为该人类可读信息设置另一列。您可以向表中添加LastModified DATETIME列,并使用DEFAULT GETDATE()约束,可以在插入时插入新值。为了使其保持最新,您必须编写AFTER UPDATE触发器以在发生任何更新时更新LastModified列。
SQL Server 2011 (a.k.a. "Denali") will bring us SEQUENCES which would be the perfect fit in your case here - but alas, that' still at least a year from official release.....
SQL Server 2011(a.k.a。“Denali”)将为我们带来SEQUENCES,这将完全适合您的情况 - 但是,唉,至少还有一年的官方发布.....