SQL Server 更新时间戳列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4592658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server updating a time stamp column
提问by Sally
In my database I have a timestampcolumn. I need to update a row in the table and need to update the timestampcolumn. When I run an update command I get:
在我的数据库中，我有一timestamp列。我需要更新表中的一行并需要更新timestamp列。当我运行更新命令时，我得到：
Cannot update a timestamp column.
How can I update the timestamp column?
如何更新时间戳列？
回答by m.edmondson
You don't
你不
The timestamp column is updated automatically. Perhaps you are under the impression that timestamp contains a value relating to the time? It doesn't, but simply is a number which is updated whenever a value in that record is. Think of it like a row version number.
该时间戳列会自动更新。也许您的印象是时间戳包含与时间相关的值？它不是，而只是一个数字，只要该记录中的值出现，就会更新该数字。 把它想象成一个行版本号。
From MSDN:
从MSDN：
The timestamp data type is just an incrementing number and does not preserve a date or a time.
时间戳数据类型只是一个递增的数字，不保留日期或时间。
回答by Damien_The_Unbeliever
You don't update the timestamp column - a timestamp (now renamed rowversion) column is automatically updated by SQL server whenever any other column in the row is updated.
您不更新时间戳列 -每当更新行中的任何其他列时，SQL 服务器都会自动更新时间戳（现在更名为rowversion）列。
If you already knew this, but for some reason want to force the column to update, just perform an update against the row you want affected. Even if it results in no actual data change, the timestamp column will still update:
如果您已经知道这一点，但由于某种原因想要强制更新列，只需对您希望受影响的行执行更新。即使它导致没有实际数据更改，时间戳列仍将更新：
create table #T1 (
ID int not null,
ts timestamp not null
)
insert into #T1 (ID)
select 1 union all
select 2
select * from #T1
update #T1 set ID = ID where ID=1
select * from #T1
ID ts
1 0x0000000000039AAF
2 0x0000000000039AB0
ID ts
1 0x0000000000039AB1
2 0x0000000000039AB0
回答by Tony
Although mine is not an answer to your question I wanted to mention how TIMESTAMPcan be misunderstood.
虽然我的不是你问题的答案，但我想提一下怎么TIMESTAMP会被误解。
You don't state your use of the TIMESTAMPcolumn in your question but they fact you are trying to update it implies (to me) you are trying to record when your data changes.
您没有TIMESTAMP在问题中说明您对该列的使用，但事实上您尝试更新它意味着（对我而言）您正在尝试记录数据更改时的情况。
Have a look at this article(there are also many others on the net) regarding using TIMESTAMPwhen you actually want to record the change using a DATETIME.
看看这篇文章（网上也有很多其他的），TIMESTAMP当你真正想要使用DATETIME.
BOL says:
BOL 说：
The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.
SQL Server 时间戳数据类型与时间或日期无关。SQL Server 时间戳是二进制数，表示数据库中数据修改发生的相对顺序。时间戳数据类型最初是为了支持 SQL Server 恢复算法而实现的。
As Damien_The_Unbeliever says the type has been renamed and the description says:
正如 Damien_The_Unbeliever 所说，该类型已被重命名，并且描述说：
The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.
rowversion 数据类型只是一个递增的数字，不保留日期或时间。要记录日期或时间，请使用 datetime2 数据类型。
Of course, if you are using the data type in the manner intended ignore all of the above :)
当然，如果您以预期的方式使用数据类型，请忽略以上所有内容:)
回答by anonymous
timestamp column cannot be updated since it is server generated and is gauranteed to be unique for the entire database - if updates were allowed, which it is not, then the value is not gauranteed to be unique.
时间戳列无法更新，因为它是服务器生成的，并且保证在整个数据库中是唯一的 - 如果允许更新，但不允许更新，则该值不能保证是唯一的。
i faced same problem trying to update the timestamp column in a replicated instance because the replicated instance did not have the same timestamp value as the publisher - which caused some problems when trying to obtain the last updated record per group of records. of course, with replication, it was a simple configurationg at the publisher to enable the same timestamp value to replicate over to the subscriber.
我在尝试更新复制实例中的时间戳列时遇到了同样的问题，因为复制实例与发布者没有相同的时间戳值 - 这在尝试获取每组记录的最后更新记录时导致了一些问题。当然，对于复制，发布者只需简单配置即可将相同的时间戳值复制到订阅者。
otherwise, there is no other way to update a timestamp value.
否则，没有其他方法可以更新时间戳值。
回答by Felipe Pincheira
I had the same problem, my solution:
我有同样的问题，我的解决方案：
UPDATE [table]
SET [ANY_COLUMN] = [ANY_COLUMN]
WHERE ID = [ID]
like anytime when a table value change the timestamp is recalculated, I update any column to the same value therefore the timestamp is updated
就像在表值更改时重新计算时间戳一样，我将任何列更新为相同的值，因此时间戳会更新
