Pros and Cons for CreatedDate and ModifiedDate columns in all database tables

Question

What are the pros and cons? When should we have them and when we shouldn't?

UPDATE

What is this comment in an update SP auto generated with RepositoryFactory? Does it have to do anything with above columns not present?

--The [dbo].[TableName] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated

Answer 1

There are pretty much no cons to having them, so if there are any chance you will need them, then add them.

People may mention performance or storage concerns but,

• in reality they will have little to no effect on SELECT performance with modern hardware, and properly specified SELECT clauses
• there can be a minor impact to write performance, but this will likley only be a concern in OLTP-type systems, and this is exactly the case where you suually want these kinds of columns
• if you are at the point where adding columns like this are a dealbreaker in terms of performance, then you are likely looking at moving away from SQL databases as a storage platform

With CreatedDate, I almost always set it up with a default value of GetDate(), so I never have to think about it. When building out my schema, I will add both of these columns unless it is a lookup table with no GUI for administering it, because I know it is unlikely the data will be kept up to date if modified manually.

Answer 2

If you don't need historical information about your data adding these columns will fill space unnecessarily and cause fewer records to fit on a page.

If you do or might need historical information then this might not be enough for your needs anyway. You might want to consider using a different system such as ValidFrom and ValidTo, and never modify or delete the data in any row, just mark it as no longer valid and create a new row.

See Wikipedia for more information on different schemes for keeping historic information about your data. The method you proposed is similar to Type 3 on that page and suffers from the same drawback that only information about the last change is recorded. I suggest you read some of the other methods too.

Answer 3

Some DBMSs provide other means to capture this information autmatically. For example Oracle Flashback or Microsoft Change Tracking / Change Data Capture. Those methods also capture more detail than just the latest modification date.

Answer 4

That column type timestamp is misleading. It has nothing to do with time, it is rowversion. It is widely used for optimistic concurrency, example here

Answer 5

All I can say is that data (or full blown audit tables) has helped me find what or who caused a major data problem. All it takes is one use to convince you that it is good to spend the extra time to keep these fields up-to-date.

I don't usually do it for tables that are only populated through a single automated process and no one else has write permissions to the table. And usually it isn't needed for lookup tables which users generally can't update either.