A colleague of mine says that SQL Server saves the date and time of last modification in a "hidden column" in each record. I am pretty sure that he said something wrong. Can you confirm this to me?
我的一个同事说，SQL Server将最后一次修改的日期和时间保存在每个记录的“隐藏列”中。我很肯定他说错了什么。你能给我确认一下吗?
5 个解决方案
#1
39
As the others are hinting at, your colleague must be talking gibberish, or referring to something else. The on-disk structure for a record, or page for that sake, does not contain any references to the time of the last update. While you can find info regarding the last update at the object level, no such info is available at the record/row level.
就像其他人在暗示的那样，你的同事一定是在胡言乱语，或者是在说别的什么。记录或页面的磁盘上结构不包含对上次更新时间的任何引用。虽然您可以在对象级别上找到关于上一次更新的信息，但是在记录/行级别上没有这样的信息。
#2
40
There is no hidden column, maintained on a per-row basis, that contains the date/time of the last modification. Nor is there a column containing the identity of the user who performed such a change, nor is there a column that identifies what the last change performed was (insert or update).
不存在每个行维护的隐藏列，它包含最后一次修改的日期/时间。也没有包含执行该更改的用户的标识的列，也没有一个列标识最后执行的更改是什么(插入或更新)。
If you want any of these features, you have to implement them. For some users, every last bit of overhead could matter to them, so having hidden features (with hidden costs) would not be acceptable.
如果您想要这些特性中的任何一个，您必须实现它们。对于一些用户来说，每一点开销都可能对他们很重要，因此拥有隐藏的特性(包含隐藏的成本)是不可接受的。
#3
10
From Pinal Dave, there is a DMV that you can use to get this information. Your colleague might be referring to the TIMESTAMP for a modified row, but as far as I know it only maintains that if you ask it to by explicitly adding a column of that type.
从皮纳尔戴夫，有一个车管所，你可以用来获取这个信息。您的同事可能指的是修改后的行的时间戳，但据我所知，它只在您通过显式地添加该类型的列来请求时才会保持这种状态。
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test');
#4
7
If you want to know when a specific row was last updated, you should maintain a "LastUpdated" timestamp column in the table and update that upon every UPDATE (e.g. automatically via trigger)
如果您想知道一个特定的行何时最后更新，您应该在表中维护一个“LastUpdated”时间戳列，并在每次更新时更新它(例如通过触发器自动更新)
#5
-2
When was a record last updated, and who updated it can be maintained by adding a last_updated column with a default value of GetDate(). The last update user name is tricky since your app is most likely using a common database connection. The user name can be filled by adding it as a customization to all insert and update procedures and passing the AD, SSO, or login credentials -- whatever makes sense for your authentication method.
最后更新的记录是什么时候，更新记录的人可以通过添加一个默认值为GetDate()的last_updates列进行维护。最后一个更新用户名很棘手，因为您的应用程序很可能使用公共数据库连接。可以将用户名作为定制添加到所有的插入和更新过程中，并传递AD、SSO或登录凭据——这对您的身份验证方法来说是有意义的。
The last update column is so helpful and necessary that it should be included as a set option for the database or at least the table. Why? Try getting this information without it, after the fact has been entered (hint, if you have an identity field -- it "may" help, but could be unreliable).
最后一个更新列是非常有用的，它应该作为数据库的set选项，或者至少是表。为什么?在输入了事实之后，尝试不使用它来获取这些信息(提示，如果您有一个标识字段——它“可能”有用，但可能不可靠)。
#1
39
As the others are hinting at, your colleague must be talking gibberish, or referring to something else. The on-disk structure for a record, or page for that sake, does not contain any references to the time of the last update. While you can find info regarding the last update at the object level, no such info is available at the record/row level.
就像其他人在暗示的那样，你的同事一定是在胡言乱语，或者是在说别的什么。记录或页面的磁盘上结构不包含对上次更新时间的任何引用。虽然您可以在对象级别上找到关于上一次更新的信息，但是在记录/行级别上没有这样的信息。
#2
40
There is no hidden column, maintained on a per-row basis, that contains the date/time of the last modification. Nor is there a column containing the identity of the user who performed such a change, nor is there a column that identifies what the last change performed was (insert or update).
不存在每个行维护的隐藏列，它包含最后一次修改的日期/时间。也没有包含执行该更改的用户的标识的列，也没有一个列标识最后执行的更改是什么(插入或更新)。
If you want any of these features, you have to implement them. For some users, every last bit of overhead could matter to them, so having hidden features (with hidden costs) would not be acceptable.
如果您想要这些特性中的任何一个，您必须实现它们。对于一些用户来说，每一点开销都可能对他们很重要，因此拥有隐藏的特性(包含隐藏的成本)是不可接受的。
#3
10
From Pinal Dave, there is a DMV that you can use to get this information. Your colleague might be referring to the TIMESTAMP for a modified row, but as far as I know it only maintains that if you ask it to by explicitly adding a column of that type.
从皮纳尔戴夫，有一个车管所，你可以用来获取这个信息。您的同事可能指的是修改后的行的时间戳，但据我所知，它只在您通过显式地添加该类型的列来请求时才会保持这种状态。
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test');
#4
7
If you want to know when a specific row was last updated, you should maintain a "LastUpdated" timestamp column in the table and update that upon every UPDATE (e.g. automatically via trigger)
如果您想知道一个特定的行何时最后更新，您应该在表中维护一个“LastUpdated”时间戳列，并在每次更新时更新它(例如通过触发器自动更新)
#5
-2
When was a record last updated, and who updated it can be maintained by adding a last_updated column with a default value of GetDate(). The last update user name is tricky since your app is most likely using a common database connection. The user name can be filled by adding it as a customization to all insert and update procedures and passing the AD, SSO, or login credentials -- whatever makes sense for your authentication method.
最后更新的记录是什么时候，更新记录的人可以通过添加一个默认值为GetDate()的last_updates列进行维护。最后一个更新用户名很棘手，因为您的应用程序很可能使用公共数据库连接。可以将用户名作为定制添加到所有的插入和更新过程中，并传递AD、SSO或登录凭据——这对您的身份验证方法来说是有意义的。
The last update column is so helpful and necessary that it should be included as a set option for the database or at least the table. Why? Try getting this information without it, after the fact has been entered (hint, if you have an identity field -- it "may" help, but could be unreliable).
最后一个更新列是非常有用的，它应该作为数据库的set选项，或者至少是表。为什么?在输入了事实之后，尝试不使用它来获取这些信息(提示，如果您有一个标识字段——它“可能”有用，但可能不可靠)。