Microsoft SQL Server:有什么方法可以判断记录是何时创建的? [英] Microsoft SQL Server: Any way to tell when a record was created?

问题描述

我们的客户希望在记录创建日期前订购.对我来说，这似乎是一个系统变量，是记录本身的某种元数据.

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.

有没有办法在不实际创建默认为 GetDate() 的日期时间字段的情况下判断记录是何时创建的，并希望没有人修改它?

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?

推荐答案

没有.

您需要为此设置一列.

想象一下，如果您需要为每条记录保留一条记录以进行创建，那么元数据会有多大！您是否还想在元数据上保留元数据，以便知道元数据何时更新?空间使用可以迅速升级.

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 会保留一些统计信息，但这种特定的信息需要来自用户定义的字段.

SQL Server keeps some stats but something this specific will need to come from a user-defined field.

作为旁注，如果您使用查找表，您可以更难以篡改所创建字段上的日期.创建一个表TableName_CreateDate"并使用实际表中的 PK 和日期值.您的日期位于单独的位置，不太可能被修改，但您仍然可以JOIN 获取您的订单.您需要创建一个触发器来使用新值更新它.

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 JOIN on it to get your order. You would need to create a trigger to update this with new values.

如果您只需要 DATE 而不需要日期时间值，您可以更进一步，只需要一个日期表和一个与之相连的查找表.即:

If you only want the DATE and 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.:

Table->Table.PK + Date.Pk ->日期表

如果您有很多行(我认为每行 4 个字节)，这将节省大量驱动器空间.

This would save a lot of drive space if you have a lot of rows (4 bytes per row I think).

这篇关于Microsoft SQL Server:有什么方法可以判断记录是何时创建的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！