Microsoft SQL Server: Any way to tell when a record was created?

Question

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?

Answer 1

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.

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.

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 -> DateTable

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