I've been following the convention of adding created and modified columns to most of my database tables. I also have been leaving the modified column as null on record creation and only setting a value on actual modification.

The other alternative is to set the modified date to be equal to created date on record creation.

I've been doing it the former way but I recent ran into one con which is seriously making me think of switching. I needed to set a database cache dependency to find out if any existing data has been changed or new data added. Instead of being able to do the following:

SELECT MAX(modified) FROM customer

I have to do this:

SELECT GREATEST(MAX(created), MAX(modified)) FROM customer

The negative being that it's a more complicated query and slower. Another thing is in file systems I believe they usually use the second convention of setting modified date = created date on creation.

What are the pros and cons of the different methods? That is, what are the issues to consider?