SQL Server 行日期上次修改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6344413/
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 row date last modified
提问by Marco Staffoli
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 将上次修改的日期和时间保存在每条记录的“隐藏列”中。我很确定他说错了什么。你能向我证实这一点吗？
回答by Damien_The_Unbeliever
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.
如果您想要这些功能中的任何一个，则必须实现它们。对于某些用户来说，最后一点开销对他们来说都很重要，因此具有隐藏功能（带有隐藏成本）是不可接受的。
回答by Mark S. Rasmussen
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.
正如其他人所暗示的那样，你的同事一定是在胡言乱语，或者指的是其他东西。记录或页面的磁盘结构不包含对上次更新时间的任何引用。虽然您可以在对象级别找到有关上次更新的信息，但在记录/行级别没有此类信息可用。
回答by Yuck
From Pinal Dave, there is a DMV that you can use to get this information. Your colleague might be referring to the TIMESTAMPfor 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.
从Pinal Dave那里，您可以使用 DMV 来获取此信息。您的同事可能指的是TIMESTAMPfor a modified row，但据我所知，它仅在您通过明确添加该类型的列要求它时才保持不变。
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');
回答by AdaTheDev
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”时间戳列，并在每次更新时更新（例如通过触发器自动更新）
回答by stan mischuk
You can try to read SQL Server transaction log using PageID.
您可以尝试使用 PageID 读取 SQL Server 事务日志。
It's not a guaranteed way, but it works in many cases , and a little more accurate than querying index statistics
这不是一种有保证的方式，但它在很多情况下都有效，并且比查询索引统计信息准确一点
/* This is example how to query datetime of data changes using SQL Server
transaction log, if audit routine wasn't set */
USE [AdventureWorksDW2017]
GO
UPDATE dbo.FactFinance
SET Date = GETDATE(), Amount = Amount + 1
WHERE FinanceKey = 1
GO
WITH main AS
(
SELECT
replace(replace(sys.fn_PhysLocFormatter (%%physloc%%), ')', ''), '(', '') page,
FinanceKey, Date, Amount
FROM
dbo.FactFinance (NOLOCK)
WHERE
FinanceKey = 1
),
tlog AS
(
SELECT
page, l2.[Begin Time], L2.[End Time],
l1.AllocUnitName l1_uname, l2.[Transaction Name] l2_tname,
l1.[Transaction ID]
FROM
main
JOIN
sys.fn_dblog(NULL, NULL) l1 ON PATINDEX('%'+main.page+'%', l1.[Lock Information]) > 0
LEFT JOIN
sys.fn_dblog(NULL, NULL) l2 ON l2.[Transaction ID] = l1.[Transaction ID]
)
SELECT
PAGE, [Transaction ID],
MIN([Begin Time]), MIN([End Time]),
MIN(l1_uname), MIN(l2_tname)
FROM
tlog
GROUP BY
[Transaction ID], page
回答by Scott
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_updated 列来维护。最后更新的用户名很棘手，因为您的应用很可能使用公共数据库连接。可以通过将用户名作为自定义添加到所有插入和更新过程并传递 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).
最后一个更新列非常有用和必要，它应该作为数据库或至少表的设置选项包含在内。为什么？在输入事实之后，尝试在没有它的情况下获取此信息（提示，如果您有身份字段——它“可能”有帮助，但可能不可靠）。
