Value of @@DBTS / timestamp column

Question

Value of @@DBTS / timestamp column

vip.blade

Right there with Babe

Points: 770
More actions
January 26, 2014 at 7:29 am

#303358

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

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 256⁸⁰⁰⁰-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 256⁸⁰⁰⁰, 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)

happycat59 One Orange Chip Points: 29387 More actions · Answer 2

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 256⁸⁰⁰⁰-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 256⁸⁰⁰⁰, 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 256⁸⁰⁰⁰-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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

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)

vip.blade Right there with Babe Points: 770 More actions · Answer 5

Thanks for all your responses, this helped me a lot!

Best regards!