Does @@DBTS ever overflow?
Posted on 2008-10-24
I'm using Timestamp columns to keep track of changes in my database and periodically process rows with a value between my previous "high water mark" and the current value of @@DBTS. I would have a start value (lower bound) to be the previously used upper bound and then the new upper bound would be the current value of @@DBTS.
This is working fine, but I was just wondering...isn't @@DBTS going to overflow at some point?
@@DBTS is BINARY(8) which is a finite size and granted it's a really big number there's still a ceiling there. Sooner or later, what I expect to happen is that the value of @@DBTS resets to zero and starts again. At that point, my existing logic would miss the changes in that batch.
I reckon I just need to be aware of this and write into my code to use two different conditions.
Normally (when start < end) use this logic:
SELECT * FROM [TableName] WHERE Timestamp > @Start AND Timestamp < @End
Overflow condition (when end < start) use this logic:
SELECT * FROM [TableName] WHERE Timestamp > @Start OR Timestamp < @End
Can anyone confirm that I should bother checking for end < start and use these two different conditions or would most people not bother? I think it's a bit academic as I may be long dead and buried before our db ever reaches that value - but I like to think of best practices. :)