Value of @@DBTS / timestamp column
-
Hi,
I have a question regarding the timestamp type in SQL Server. As far as I know the timestamp column holds an ever increasing number (every update or insert increments the @@DBTS value and writes this to the inserted or updated row). Now I have the question, when or if this global value gets resetted or if it is ever increasing, also after a restart of the sql server?
Best regards!
-
vip.blade (1/26/2014)
Hi,I have a question regarding the timestamp type in SQL Server. As far as I know the timestamp column holds an ever increasing number (every update or insert increments the @@DBTS value and writes this to the inserted or updated row). Now I have the question, when or if this global value gets resetted or if it is ever increasing, also after a restart of the sql server?
Best regards!
The following URL...
http://technet.microsoft.com/en-us/library/ms187366.aspx
... says...
This timestamp is guaranteed to be unique in the database.
Since the datatype is VARBINARY and, not including MAX datatypes, would allow a value of 2568000-1 as well as the guarantee of uniqueness in a database, I'm thinking that it will never be reset and that a restart of SQAL Server will also not reset it.As for what happens when it does reach a value of 2568000, I suspect that even the fastest computer couldn't reach that mark in hundreds of lifetimes.[Edit] Please see corrected information further below.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/26/2014)
vip.blade (1/26/2014)
Hi,I have a question regarding the timestamp type in SQL Server. As far as I know the timestamp column holds an ever increasing number (every update or insert increments the @@DBTS value and writes this to the inserted or updated row). Now I have the question, when or if this global value gets resetted or if it is ever increasing, also after a restart of the sql server?
Best regards!
The following URL...
http://technet.microsoft.com/en-us/library/ms187366.aspx
... says...
This timestamp is guaranteed to be unique in the database.
Since the datatype is VARBINARY and, not including MAX datatypes, would allow a value of 2568000-1 as well as the guarantee of uniqueness in a database, I'm thinking that it will never be reset and that a restart of SQAL Server will also not reset it.
As for what happens when it does reach a value of 2568000, I suspect that even the fastest computer couldn't reach that mark in hundreds of lifetimes.
Mmm - the storage for a TIMESTAMP column is actually 8 bytes - equivalent of a BINARY(8). If definite does not allow for values up to 2568000-1.
As for uniqueness - yes, the values are unique within a database. If you backup/restore the database to another server or as a database with a new name on the same instance, the next value generated on both the original database and the restored database will be the same.
-
Since a TIMESTAMP column is 64 bits, the number of possible unique values = 18,446,744,073,709,551,616 ( 2 to the 64th power ).
Certainly a very large number.
A table with a single byte with that number of rows would be about 134,217,728 TBytes:
-- ((2 to 64th) * 8)/ (1099511627776) /* bytes per TB */select (power(2.0E,64.0E)*8.0E) / power(1024.0E,4.0E)That is a DB size well beyond the ability of the current technology to implement (1.3 million 100 TByte drives), but not impossible to imagine in the future.
-
Ah, my apologies. I read that the return datatype of @@DBTS is VARBINARY in BOL and didn't consider the source (TIMESTAMP) is 64 bits. Thanks for the correction.
Guess we'll have to settle for just for a lifetime or two.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for all your responses, this helped me a lot!
Best regards!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply