From the documentation
This all sounds reasonable, but what happens if the system clock is changed (for example set back due to a time server synchronization). Can this cause a situation where either
This seems possible given the last sentence suggests that it is only compared against the previous value, so if the DEFAULT TIMESTAMP value does go backwards due to a clock change, it could conceivably produce the same value twice.
Well, according to some tests with 18.104.22.16842, the database engine seems to make sure that DEFAULT TIMESTAMP values are increasing monotonically - as long as the database engine is running.
I.e. when using a local database engine and modifying the system clock, it seems that
In contrast, SELECT GETDATE() will always return the system time: When the system clock is set back to yesterday, it will display that. Therefore DEFAULT TIMESTAMP may be ahead of the system time...
However, when the engine is shutdown and restarted after the system clock has been set to a former time, it will happily use that system time for new DEFAULT TIMESTAMP values.
Given that, I would think that DEFAULT TIMESTAMP does protect a running system against decreasing values but does not guarantee that when the engine (or only the database?) is shutdown in-between.
If you are primarily worried over influences of time synchronization, then I would think that this should not be a problem:
Just a switch between standard time and daylight saving time might need particular care - if so, feel free to use DEFAULT UTC TIMESTAMP:)
If you want something unique from the database system use an autoincrement field or a GUID field. Or you can force uniqness of the timestamp field by declaring a unique index on it (anyway this may reject otherwise valid inserts ;-) ). Doing so would at least identify a system clock problem.
answered 09 Oct '13, 02:57