1

I have a problem about the timestamp/rowversion overflow. As to my understanding, the use of the datatype would be trivial with the overflow happening as we cannot differentiate the oldest and the latest modified entries in a database. My problem is how can I overcome this?

Edit: Let's say I'm using timestamp for syncing data, How can I differentiate an entry with timestamp 0x0000000000000001 [first entry of the database] and the entry after the 0xfff....fff [first entry after the overflow]. ( as to my understanding, this field should eventually overflow at some point in time.please correct me if I'm wrong).

SDK
  • 175
  • 1
  • 15
  • 1
    The fact you need to do this, suggests your real problem is actually something else.... – Mitch Wheat Jun 13 '12 at 05:16
  • I wonder what happens when a timestamp overflows. It would mean, we cannot use the timestamp for any longer as earliest entries and the latest entries would contain same timestamp. I hope this makes sense. :) – SDK Jun 13 '12 at 05:18
  • Can't we use timestamp for check the modified data after some point in time and use that for sync data ( only retrieving the modified data since the last check.) – SDK Jun 13 '12 at 05:21
  • Yes. That's what it is designed for. – Mitch Wheat Jun 13 '12 at 05:30
  • If there are many changes to the db, the counter would overflow, then either the timestamps would have to remain in a MAX value or would have to overflow(I'm guessing it would overflow). So the earliest entries of the db and the latest entries which are modified will have timestamps near 0x00...00 which would mean comparison between them would give wrong results.( I know timestamp supports larger values, but still it could overflow.) – SDK Jun 13 '12 at 05:40
  • 3
    many changes? you mean 2^31 - 1 changes? That's alot of chnages! I believe rowversion simply wraps without error (but cannot find a reference in the docs) – Mitch Wheat Jun 13 '12 at 05:48
  • I asked because in [link](http://stackoverflow.com/a/547782/1152047) it is mentioned. So I had to ask as I have no experience with db's with that much updates. Guess it wouldn't become a practical problem. thanks. – SDK Jun 13 '12 at 06:18
  • again, that's someone's advice, not the official docs. It's what I assume it does. – Mitch Wheat Jun 13 '12 at 06:24
  • If you need it to sync data, then it shouldn't be a problem whether it overflows or not, as it should still be only a secondary key to your row - and you only have to check if the rows with the same primary key has the same timestamp or not. But, as it says in the docs, if you need to record the actual time, you should use a datetime2. –  Jun 13 '12 at 06:48
  • I think it's an unsigned 64bit integer. Meaning 2^64 - 1 changes. 18,446,744,073,709,551,615! ;) http://msdn.microsoft.com/en-gb/library/ms182776(v=sql.100).aspx – RoboJ1M Oct 24 '14 at 09:36

0 Answers0