Microsoft SQL Server：有什么方法可以判断记录是何时创建的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4199040/
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
Microsoft SQL Server: Any way to tell when a record was created?
提问by Zachary Scott
Our customer wants to order by the record creation date. To me this seems like a system variable, some sort of meta data for the record itself.
我们的客户希望在记录创建日期前订购。对我来说，这似乎是一个系统变量，是记录本身的某种元数据。
Is there a way to tell when a record was created without actually creating a datetime field with a default of GetDate() and hope that no one modifies it?
有没有办法在不实际创建默认为 GetDate() 的日期时间字段的情况下判断记录是何时创建的，并希望没有人修改它？
回答by JNK
Nope.
不。
You need to have a column for this.
您需要为此设置一列。
Imagine how big the meta-data would be if you needed to keep a record for each record for creation! Would you also want to keep meta-data on your meta-data so you know when the meta-data was updated? The space use can quickly escalate.
想象一下，如果您需要为每条记录保留一条记录以进行创建，那么元数据会有多大！您是否还想在元数据上保留元数据，以便知道元数据何时更新？空间使用可以迅速升级。
SQL Server keeps some stats but something this specific will need to come from a user-defined field.
SQL Server 会保留一些统计信息，但这种特定的信息需要来自用户定义的字段。
As a side note, you can make it more difficult to tamper with the date on your created field if you use a lookup table. Create a table "TableName_CreateDate" and use the PK from your actual table and a date value. Your date is in a separate location and less likely to be modified but you can still JOINon it to get your order. You would need to create a trigger to update this with new values.
作为旁注，如果您使用查找表，您可以更难以篡改创建字段上的日期。创建一个表“TableName_CreateDate”并使用实际表中的 PK 和日期值。您的日期在一个单独的位置，不太可能被修改，但您仍然可以JOIN在它上面得到您的订单。您需要创建一个触发器来使用新值更新它。
If you only want the DATEand don't need a datetime value, you can go one step further and just have a table of dates and a lookup table that joins to that. I.e.:
如果您只需要DATE而不需要日期时间值，您可以更进一步，只需要一个日期表和一个与之相连的查找表。IE：
Table->Table.PK + Date.Pk -> DateTable
Table->Table.PK + Date.Pk -> DateTable
This would save a lot of drive space if you have a lot of rows (4 bytes per row I think).
如果您有很多行（我认为每行 4 个字节），这将节省大量驱动器空间。
回答by HLGEM
The creationdate field should be added. You fix the ' hope that no one modifies it" part by adding a trigger on update that does not allow that field to be updated, ever.
应添加创建日期字段。您通过在更新时添加一个不允许更新该字段的触发器来修复“希望没有人修改它”的部分。
回答by Ta01
No, not even in the system tables to my knowledge. You could dig through old transaction logs I'm sure, but natively, no.
不，据我所知，即使在系统表中也没有。我敢肯定，您可以翻阅旧的事务日志，但就本机而言，不可以。
回答by FShieldheart
I once came with a kind of situation where order of the rows of a table is required but no timestamp (CreateDateTime, UpdateDateTime, ..., etc.) column is available. So, only data I had was the transaction log. Reading transaction log directly was not helpful as it is not easy to parse. Hence, I found a program called "ApexSQL Log" which was very helpful. It has all filtering options and you can read all log data without hardcore operations. Although this solution was satisfactory for me, it has a drawback: You can only filter date (Begin Time and End Time) according to transaction operation. If you are searching for a query operation, for example an insert, which is encapsulated in a transaction, you can not retrieve the exact insert time (at least I could not find a way), only the transaction time. If your operations are all atomic, then you can literaly have all row create date information.
我曾经遇到过一种情况，需要表的行顺序，但没有时间戳（CreateDateTime、UpdateDateTime 等）列可用。因此，我拥有的唯一数据是事务日志。直接读取事务日志没有帮助，因为它不容易解析。因此，我找到了一个名为“ApexSQL Log”的程序，它非常有用。它具有所有过滤选项，您无需硬核操作即可读取所有日志数据。虽然这个方案让我满意，但它有一个缺点：只能根据事务操作过滤日期（开始时间和结束时间）。如果你在搜索一个查询操作，例如一个插入，它被封装在一个事务中，你无法检索确切的插入时间（至少我找不到方法），只能检索事务时间。如果您的操作都是原子操作，
