timestamp column
-
Do you recommend having a timestamp column in each table in your database? I've never really understood what exactly the timestamp is used for, and I haven't seen any "best practices" recommendations regarding whether or not to put it in each table. Any input you can provide would be helpful.
-
Reference tables "may" not need it. They should rarely be updated or inserted and only a small handful of people should be able to access them.
Production tables should not only have a timestamp, but a User column as well. I have contracted to a number of accounting and accounting-like companies and it is very important to know who last touched the data.
So often people view this as a way to come down on employees. The truth is, in a well run business, knowing who did something is the best training. People make mistakes, but they cannot learn how to do it right if you don't know who did it to teach them the right thing. Timestamping with a UserName is a good way to keep up with changes to your database and who made the changes. If you have a lot of changes to one record throughout the day, you may never find out who made the change.
This means that on certain tables you may have to strategize on just how you track changes. But without a timestamp and some sort of personal tracking mechanism, you have no possibility of doing that without extensive review of the transaction log.
It also is good for tracking that tiny percentage who really do mean to do malice. It sucks, but they are out there...
I wasn't born stupid - I had to study.
-
Well, in this particular case, I'm talking about a quasi-data warehouse that gets refreshed each week using bulk loading processes. During the week, it just gets queried, with no updates to it, so we don't need to track any individual updates to the data.
The "timestamp" datatype is what is confusing me. Is there any reason to have a column of type "timestamp" in the tables in in our database, as described?
-
Well now, don't I feel dopey.... I go off on a diatribe and it sounds like you don't even need that kind of column.
If nothing is ever really altered, I see no reason to have it. You may want to set up a table to count how many people access your data, but a timestamp seems excessive.
I wasn't born stupid - I had to study.
-
The timestamp datatype is provided by SQL Server as a simple way of versioning records in a table - among other things, it enables you to track which rows have been updated, because when a row is updated, the value of that row's timestamp column changes automatically.
So you can compare current data with that from a backup with a quick one-field query to see what has been changed.
Having said that, it doesn't tell you who did the update or when, which, as Farrell mentioned, is probably what you want if you need to audit changes to your data. I've never needed to use it.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
All of our data warehouse tables have four columns added:
Audit_Create_Date, Audit_Create_UID, Audit_Modify_Date, and Audit_Modify_UID
We use these on the truncate and load tables also. Why? It is just our "standard." We do not use the timestamp data type. We use the datetime data type instead.
-
We always add the Add_Date, Added_By, Update_Date and Update_By columns for audit trail purposes. Add_Date and Update_date are data type Datetime. We also use Version (datatype integer) to determine how often a record has been updated and Timestamp (datatype timestamp). The data type timestamp has nothing to do with "time". We use timestamp for comparison to determine if a record has been updated by another process between the time the user displays the record and the user updates it.
Terri
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply