Tech Support customers asked us, “Is the value of a timestamp guaranteed to be monotonically increasing within a database?

0
Posted

Tech Support customers asked us, “Is the value of a timestamp guaranteed to be monotonically increasing within a database?

0

These customers wanted to use a timestamp field to identify rows that have changed since the last time they looked, with a where clause like where timestamp > @previous_timestamp. The wanted just to add a timestamp field to their existing tables, so that SQL Server would maintain the values, and they wouldn’t have to change any of their own code to identify newly inserted/updated rows. (Deleted rows would need to be treated differently.) The answer is that the timestamp is not guaranteed to increase indefinitely. A timestamp is a 56-bit integer that will eventually roll over, though it takes a very long time to do so. The only numbers that won’t roll over are ones with unbounded storage; there is no such datatype in SQL Server. However, Sybase does guarantee that if the row has changed, the timestamp will differ from the one in the cached copy of the row. SQL Monitor Client/Server and SQL Server Compatibility The following table is a compatibility matrix for various SQL Monitor Client,

Related Questions